import com.lowagie.text.DocumentException
import com.lowagie.text.pdf.*
import groovy.json.JsonSlurper
import org.apache.http.HttpEntity
import org.apache.http.client.methods.CloseableHttpResponse
import org.apache.http.client.methods.HttpGet
import org.apache.http.client.methods.HttpPost
import org.apache.http.entity.StringEntity
import org.apache.http.impl.client.CloseableHttpClient
import org.apache.http.impl.client.HttpClients
import org.apache.http.util.EntityUtils
import org.primefaces.PrimeFaces
import org.primefaces.event.FileUploadEvent
import org.primefaces.event.SelectEvent
import org.primefaces.model.DefaultStreamedContent
import org.primefaces.model.DefaultTreeNode
import org.primefaces.model.TreeNode
import org.primefaces.model.file.UploadedFile
import org.primefaces.util.SerializableSupplier
import wsd.appsys.sp.BackBean
import wsd.appsys.sp.SendBean
import wsd.appsys.sp.SpUtuils
import wsd.sys.jdbcManager
import wsd.sys.singlecz.singleczbean

import javax.faces.context.FacesContext
import java.awt.*
import java.text.SimpleDateFormat
import java.util.List;

public execute(o) {
    return new mc(o)
}

class mc {
    def mypara
    def list = []
    def xzlist = []
    def ylrs//文件预览对话框开关
    def ylfile//文件预览对应模型
    def xiugai = true;//修改按钮默认关闭
    def shanchu = true;//删除按钮默认关闭
    def shenhe = true;//审核按钮默认关闭
    def qishen = true;//弃审按钮默认关闭
    def fjyl = true;//附件预览按钮默认关闭
    def baoxiao = true;//报销类型默认关闭
    def tj = true;//提交按钮默认关闭
    def fjsc = true;//附件上传按钮默认关闭
    def fjxz = null//附件选中
    def update = true;//关联合同按钮控制默认关闭
    public mc(m) {
        this.mypara = m;
        dtxslx2()
        selectfun()
    }
    //表头变量集合
    def datalist = [id: null, htid: null, ccode: null, reportingTime: null, rycode: null, informant: null, constatus: null, departCode: null, department: null, code: null, cusCode: null, cusName: null, totalCost: null, reimbursementType: null, creatorCode: null, creator: null, creationtime: null, modifier: null, modifiedtime: null, reviewer: null, auditTime: null, def1: null, def2: null, def3: null, def4: null, def5: null, def6: null, def7: null, def8: null, def9: null, def10: null, def11: null, def12: null, def13: null, def14: null, def15: null, def16: null];
    def itemlist = [];//费用单表体查询集合
    def filelist = [];//费用单附件集合
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-02-15
     * 方法作用：费用单表头 （分页调用方法）。
     * 方法参数：whe（where拼接字符）
     * 返回值：
     * */

    def select(whe) {
        def conn = null;
        def conn2 = null;
        list = [];
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            conn2 = groovy.sql.Sql.newInstance(mypara.hqcon(1));
            def selectSql = '''select a.id,a.htid,a.ccode,a.reportingTime,a.rycode,c.name as informant,a.departCode,d.name as department,a.constatus,a.code,
                             a.cusCode,b.cCusName as cusName,a.totalCost,a.reimbursementType,a.def1,a.def2,a.def3,a.def4,
                             a.def5,a.def6,a.def7,a.def8,a.def9,a.def10,a.def11,a.def12,a.def13,a.def14,a.def15,a.def16,
                             a.creatorCode,a.creator,a.creationtime,a.modifier,a.modifiedtime,a.reviewer,
                             CONVERT (VARCHAR(16),a.auditTime,120) as auditTime from YZ_CRM_Cost a 
                             left join YZ_CRM_Customer b on a.cusCode=b.ID 
                             left join YZ_CRM_Psndoc c on a.rycode=c.id
                             left join YZ_CRM_Dept d on a.departCode=d.id
                             where a.def1=0 ''' + whe + ''' '''
            conn.eachRow(selectSql) {
                //查询创建人
                def cjr = conn.firstRow("select creatorCode from YZ_CRM_Cost where id=" + it.id + " ")
                def a = qxkz('expense', conn2, it.id, 'expense', mypara.set.user.code, cjr.creatorCode)
                if (a == 1) {
                    def t = ["id": it.id, "htid": it.htid, "ccode": it.ccode, "reportingTime": it.reportingTime, "rycode": it.rycode, "informant": it.informant, "departCode": it.departCode, "department": it.department, "constatus": it.constatus, "code": it.code, "cusCode": it.cusCode, "cusName": it.cusName, "totalCost": it.totalCost, "reimbursementType": it.reimbursementType, "def1": it.def1, "def2": it.def2, "def3": it.def3, "def4": it.def4, "def5": it.def5, "def6": it.def6, "def7": it.def7, "def8": it.def8, "def9": it.def9, "def10": it.def10, "def11": it.def11, "def12": it.def12, "def13": it.def13, "def14": it.def14, "def15": it.def15, "def16": it.def16, "creator": it.creator, "creatorCode": it.creatorCode, "creationtime": it.creationtime, "modifier": it.modifier, "modifiedtime": it.modifiedtime, "reviewer": it.reviewer, "auditTime": it.auditTime]
                    list.add(t)
                }
            }
            if (list.size() == 0) {
                datalist = [id: null, htid: null, ccode: null, reportingTime: null, rycode: null, informant: null, constatus: null, departCode: null, department: null, code: null, cusCode: null, cusName: null, totalCost: null, reimbursementType: null, creatorCode: null, creator: null, creationtime: null, modifier: null, modifiedtime: null, reviewer: null, auditTime: null, def1: null, def2: null, def3: null, def4: null, def5: null, def6: null, def7: null, def8: null, def9: null, def10: null, def11: null, def12: null, def13: null, def14: null, def15: null, def16: null];
            } else {
                def idstr = "(0";
                for (int i = 0; i < list.size(); i++) {
                    idstr = idstr + "," + list.get(i).get("id");
                }
                idstr = idstr + ")";
                selectitemfun(idstr)
                selecfilefun(idstr)
                datalist = list.get(0)//表头集合
                itemlist = list.get(0).get("item")//表体查询集合
                filelist = list.get(0).get("file")//附件查询集合
                buttonControl()
            }
        } catch (Exception e) {
            e.printStackTrace()
            mypara.msg("查询失败！")
        } finally {
            if (conn != null) {
                conn.close()
            }
            if (conn2 != null) {
                conn2.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：费用单表体
     * 方法参数：whe（where拼接字符）
     * 返回值：
     * */

    def selectitemfun(id) {
        def conn = null;
        itemlist = [];
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def selectSql = "SELECT a.id,a.nid,a.expenseType,a.purpose,isnull(a.money,0) as money,a.remark,a.enclosure,a.vdef1,a.vdef2,a.vdef3,a.vdef4,a.vdef5,a.vdef6,a.vdef7,a.vdef8,a.vdef9,a.vdef10,a.vdef11,a.vdef12,a.vdef13 ,a.vdef14,a.vdef15,a.vdef16 " +
                    " FROM [dbo].[YZ_CRM_Costs] a " +
                    " left join [dbo].[YZ_CRM_Cost] b on a.nid=b.id " +
                    " left join [dbo].[YZ_CRM_ExpenseType] c on a.expenseType=c.id where isnull(a.vdef1,0)!=1 and a.nid in" + id
            itemlist = conn.rows(selectSql)
            for (int i = 0; i < list.size(); i++) {
                def lists = []
                for (int j = 0; j < itemlist.size(); j++) {
                    if (list.get(i).get("id") == itemlist.get(j).get("nid")) {
                        lists.add(itemlist.get(j));
                    }
                }
                list.get(i).put("item", lists);
            }
        } catch (Exception e) {
            e.printStackTrace()
            mypara.msg("查询失败！")
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：费用单附件信息
     * 方法参数：whe（where拼接字符）
     * 返回值：
     * */

    def selecfilefun(id) {
        def conn = null;
        filelist = [];
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def selectSql = " select f.id,f.filename,f.filepath,f.fyid " +
                    " FROM [dbo].[YZ_CRM_file] f " +
                    " left join [dbo].[YZ_CRM_Cost] a on a.id=f.fyid where a.def1=0 and f.fyid in" + id
            filelist = conn.rows(selectSql)
            for (int i = 0; i < list.size(); i++) {
                def lists = []
                for (int j = 0; j < filelist.size(); j++) {
                    if (list.get(i).get("id") == filelist.get(j).get("fyid")) {
                        lists.add(filelist.get(j));
                    }
                }
                list.get(i).put("file", lists);
            }
        } catch (Exception e) {
            e.printStackTrace()
            mypara.msg("查询失败！")
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：部门树形参照方法。
     * 方法参数：
     * 返回值：
     * */
    def TreeNode root;
    def TreeNode selectedNode = null;

    def selectfun() {
        def conn = null;
        def lists = [];
        try {
            def selectSql = ''' select convert(nvarchar,id) as id,code,name,removestatus,removetime,convert(nvarchar,fatherdept) as fatherdept,creator,creationtime,modifier,modifiedtime,
                                def1,def2,def3,def4,def5,def6,def7,def8,def9,def10,def11,def12,def13,def14,def15,def16 from [dbo].[YZ_CRM_Dept] where removestatus=1'''
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def list = conn.rows(selectSql)
            lists = xklist(list)
        } catch (Exception e) {
            e.printStackTrace()
            mypara.msg("查询失败！" + e)
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
        root = new DefaultTreeNode([id: '0', code: null, name: null, removestatus: null, removetime: null, fatherdept: null, creator: null, creationtime: null, modifier: null, modifiedtime: null, def1: null, def2: null, def3: null, def4: null, def5: null, def6: null, def7: null, def8: null, def9: null, def10: null, def11: null, def12: null, def13: null, def14: null, def15: null, def16: null], null);
        root = dinclv(lists, root)
    }

    def dinclv(xh, rootl) {
        for (i in xh) {//重复调用直到完成返回数据
            TreeNode documents = new DefaultTreeNode(i, rootl);
            if (i['x']) {
                dinclv(i['x'], documents)
            }
        }
        return rootl
    }

    def xklist(vl) {
        def vcf = [:]//将数据库数据修改为map方式，键为id整条数据为值。
        def vcv = [] //返回数据
        for (i in vl) {
            vcf.put(i.id.toString(), i)
            i.put("x", [])
        }
        for (i in vl) {//生成树结构将无上级的数据掺入返回数据
            if (i.fatherdept) {
                vcf[i.fatherdept]["x"].add(i)
            } else {
                vcv.add(i)
            }
        }
        return vcv
    }

    def assignme() {
        if (selectedNode == null) {
            mypara.msg("没有选择！")
            return
        } else {
            if (selectedNode.getData()) {
                datalist['departCode'] = selectedNode.getData().id
                datalist['department'] = selectedNode.getData().name
                sczry = new singleczbean();//人员
            } else {
                mypara.msg("没有选择！")
                return
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-16
     * 方法作用：自动完成部门档案。
     * 方法参数：
     * 返回值：
     * */

    def czbmda(str) {
        def conn = null
        def arr = []
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def chaxun = "select name as tbbm from [dbo].[YZ_CRM_Dept] where name like '%" + str + "%' "
            conn.eachRow(chaxun) {
                arr = arr + it.tbbm
            }
            return arr
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-16
     * 方法作用：自动完成人员档案。
     * 方法参数：
     * 返回值：
     * */

    def czdryda(str) {
        def conn = null
        def arr = []
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def chaxun = "select name as tbr from [dbo].[YZ_CRM_Psndoc] where name like '%" + str + "%' "
            conn.eachRow(chaxun) {
                arr = arr + it.tbr
            }
            return arr
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-06-27
     * 方法作用：初始化人员参照。
     * 方法参数：
     * 返回值：
     * */

    def cscz() {
        sczry = new singleczbean();//人员参照
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：insert新增费用单方法。
     * 方法参数：
     * 返回值：
     * */
    def lbif = true;//列表是否可编辑
    def butone = true;//按钮不可点击
    def buttwo = false;//按钮可点击
    def rycode
    def departCode
    def department
    def informant

    def insertfun() {
        cscz()
        xiugai = true;
        shanchu = true;
        tj = true;
        lbif = false;
        butone = false;
        buttwo = true;
        baoxiao = false;
        shenhe = true;
        fjyl = true;
        fjsc = true;
        update = true;
        def conn = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            java.text.SimpleDateFormat formatterid = new java.text.SimpleDateFormat("yyyyMMdd")
            def nowtimeid = formatterid.format(new Date())//当前时间
            int maxid = getNowIdfun(nowtimeid);//获取合同编码最大值+1
            def codelen = '000'//code长度
            int strc = codelen.length() - String.valueOf(maxid).length();//获取长度差
            def code = codelen.substring(0, strc) + maxid//拼接生成当前单据id
            code = nowtimeid + code;

            //获取当前登录人和部门
            def user = mypara.getSet().getUser().name
            java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd")
            def nowtime = formatter.format(new Date())//当前时间
            //通过登录人查询部门档案和人员档案
            def chaxun = conn.firstRow("select a.id as ryid,a.name as ryname,b.id as bmid,b.name as bmname from [YZ_CRM].[dbo].[YZ_CRM_Psndoc] a left join [YZ_CRM].[dbo].[YZ_CRM_Dept] b on a.dept_id=b.id where a.name='" + user + "'")
            //            println chaxun
            def id = jdbcManager.getsqnid()
            if (chaxun == null) {
                sczry = new singleczbean();//人员参照
                datalist = [id: null, ccode: code, reportingTime: nowtime, rycode: null, informant: null, departCode: null, department: null, constatus: 6, code: null, cusCode: null, cusName: null, totalCost: 0.00, reimbursementType: null, creatorCode: null, creator: null, creationtime: null, modifier: null, modifiedtime: null, reviewer: null, auditTime: null, def1: null, def2: null, def3: null, def4: null, def5: null, def6: null, def7: null, def8: null, def9: null, def10: null, def11: null, def12: null, def13: null, def14: null, def15: null, def16: null];
                itemlist = [[id: id, nid: null, expenseType: null, purpose: null, money: 0.00, remark: null, enclosure: null, vdef1: null, vdef2: null, vdef3: null, vdef4: null, vdef5: null, vdef6: null, vdef7: null, vdef8: null, vdef9: null, vdef10: null, vdef11: null, vdef12: null, vdef13: null, vdef14: null, vdef15: null, vdef16: null]]
//表体的查询集合
            } else {
                sczry = new singleczbean();//人员参照
                sczry.setNid(chaxun.ryid.toString())
                sczry.setName(chaxun.ryname)
                rycode = chaxun.ryid.toString()
                departCode = chaxun.bmid
                department = chaxun.bmname //回款部门
                informant = chaxun.ryname
                datalist = [id: null, ccode: code, reportingTime: nowtime, rycode: rycode, informant: informant, departCode: departCode, department: department, constatus: 6, code: null, cusCode: null, cusName: null, totalCost: 0.00, reimbursementType: null, creatorCode: null, creator: null, creationtime: null, modifier: null, modifiedtime: null, reviewer: null, auditTime: null, def1: null, def2: null, def3: null, def4: null, def5: null, def6: null, def7: null, def8: null, def9: null, def10: null, def11: null, def12: null, def13: null, def14: null, def15: null, def16: null];
                itemlist = [[id: id, nid: null, expenseType: null, purpose: null, money: 0.00, remark: null, enclosure: null, vdef1: null, vdef2: null, vdef3: null, vdef4: null, vdef5: null, vdef6: null, vdef7: null, vdef8: null, vdef9: null, vdef10: null, vdef11: null, vdef12: null, vdef13: null, vdef14: null, vdef15: null, vdef16: null]]
//表体的查询集合
            }
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-02-24
     * 方法作用：获取当前表最大值+1。
     * 方法参数：tName（表名）
     * 返回值：最大值加1
     * */

    def getNowIdfun(String tName) {
        def conn = null;
        def id = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def selectSql = "select id from [dbo].[Cost_TableId] where tName='" + tName + "'"
            def map = conn.firstRow(selectSql)
            if (map) {
                id = map.get("id") + 1;
                def updateSql = "update Cost_TableId set id='" + id + "' where tName='" + tName + "'"
                conn.execute(updateSql);
            } else {
                id = 1;
                def updateSql = "insert into Cost_TableId (tName,id) values ('" + tName + "',1);"
                conn.execute(updateSql);
            }
            return id;
            println(2222)
        } catch (Exception e) {
            e.printStackTrace()
            mypara.msg("查询失败！")
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：人员参照change方法。
     * 方法参数：
     * 返回值：
     * */
    def sczry = new singleczbean()//提报人
    def change(only) {
        if (sczry == null) {
            sczry = new singleczbean();
        }
        if (only.toString().equals("RYCZ")) {
            if (sczry) {
                datalist['rycode'] = sczry.getNid();//人员ID
                if (datalist['department'] == null) {//判断部门是否为空，为空则选择人员同步更新部门，不为空则不更新部门。
                    bumenfun(datalist['rycode'])
                    org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:bumen");
                }
            } else {
                datalist['rycode'] = null;//人员ID
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：根据选择用户查询所在部门。
     * 方法参数：用户id
     * 返回值：
     * */

    def bumenfun(def rycode) {
        def conn = null;
        try {
            def selectSql = ''' select p.id as id,p.name as ryname,d.code as bmcode,d.name as bmname from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.dept_id=d.id where p.id=''' + rycode + ''' '''
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def list = conn.firstRow(selectSql)
            sczry = new singleczbean()
            sczry.setNid(list.get("id").toString())
            sczry.setName(list.get("ryname"))
            datalist['informant'] = list.get("ryname");
            datalist['departCode'] = list.get("bmcode");
            datalist['department'] = list.get("bmname");
        } catch (Exception e) {
            e.printStackTrace()
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：表体增行方法
     * 方法参数：
     * 返回值：
     * */

    def itemzhfun() {
        def id = jdbcManager.getsqnid()
        def map = [id: id, nid: null, expenseType: null, purpose: null, money: 0.00, remark: null, enclosure: null, vdef1: null, vdef2: null, vdef3: null, vdef4: null, vdef5: null, vdef6: null, vdef7: null, vdef8: null, vdef9: null, vdef10: null, vdef11: null, vdef12: null, vdef13: null, vdef14: null, vdef15: null, vdef16: null]
        itemlist.add(map);
    }

    List itemtabxz = new ArrayList<>()//多选删除表体
    def itemtremoveList = []
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：表体删行方法
     * 方法参数：
     * 返回值：
     * */

    def itemshfun() {
        if (itemtabxz.size() != 0) {
            for (int i = 0; i < itemtabxz.size(); i++) {
                itemtremoveList.add(itemtabxz.get(i));
            }
            itemlist = itemlist - itemtabxz;
            itemtabxz = new ArrayList<>();
            gb()
        } else {
            mypara.msg('未选择删除数据。')
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：保存
     * 方法参数：
     * 返回值：
     * */

    def savefun() {
        try {
            def returnstr = "";
            if (datalist.get("reimbursementType") == '0') {
                returnstr = returnstr + "请选择报销类型！"
            }
            if (datalist.get("reimbursementType") == '2') {
                if (datalist.get("code").equals('') || datalist.get("code") == null) {
                    returnstr = returnstr + "报销类型为项目费用中的合同不可为空！"
                }
                if (datalist.get("cusName").equals('') || datalist.get("cusName") == null) {
                    returnstr = returnstr + "报销类型为项目费用中的客户名称不可为空！"
                }
            }
            if (datalist.get("reimbursementType") == '3') {
                if (datalist.get("code").equals('') || datalist.get("code") == null) {
                    returnstr = returnstr + "报销类型为客户费用中合同不可为空！"
                }
                if (datalist.get("cusName").equals('') || datalist.get("cusName") == null) {
                    returnstr = returnstr + "报销类型为客户费用中的客户名称不可为空！"
                }
            }
            if (datalist.get("reimbursementType") == '4') {
                if (datalist.get("cusName").equals('') || datalist.get("cusName") == null) {
                    returnstr = returnstr + "报销类型为合同前费用客户名称不可为空！"
                }
            }
            if (datalist.get("informant").equals('') || datalist.get("informant") == null) {
                returnstr = returnstr + "提报人不可为空！"
            }
            if (datalist.get("department").equals('') || datalist.get("department") == null) {
                returnstr = returnstr + "提报部门不可为空！"
            }
            if (itemlist.size() == 0) {
                returnstr = returnstr + "表体不可为空！"
            }
            for (int i = 0; i < itemlist.size(); i++) {
                if (itemlist.get(i).get("money") == null || itemlist.get(i).get("money").toString().equals("")) {
                    returnstr = returnstr + "表体第" + (i + 1) + "行金额不可为空！"
                }
                if (itemlist.get(i).get("purpose") == null || itemlist.get(i).get("purpose").toString().equals("")) {
                    returnstr = returnstr + "表体第" + (i + 1) + "行用途不可为空！"
                }
            }
            if (returnstr.length() > 0) {
                mypara.msg(returnstr)
                if (sczry == null) {
                    sczry = new singleczbean();
                }
                return
            } else {
                if (datalist.get("id") == null) {
                    insertSQL()
                } else {
                    if (datalist.get("reimbursementType") == '4') {
                        if (datalist.get("ccode") == null) {
                            mypara.msg("请选择合同后再保存！")
                        } else {
                            htqupdateSQL();
                        }
                    } else {
                        updateSQL()
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace()
            mypara.msg("保存失败！")
        }
        select(" and a.id=" + datalist.get("id") + " ");
    }
    SimpleDateFormat sdf_draft = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")
    /**
     * @author: 肖兴顺
     * @description: 合同前费用关联合同保存后反写合同项目费用
     * @date: 2023-08-10 9:29
     * @param:
     * @return:
     * */
    def htqupdateSQL() {
        def conn = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            conn.connection.autoCommit = false;
            //费用单头====================================================
            def updateField = ['htid', 'ccode', 'reportingTime', 'rycode', 'informant', 'departCode', 'department', 'constatus', 'reimbursementType', 'code', 'cusCode', 'cusName', 'totalCost', 'creator', 'creationtime', 'modifier', 'modifiedtime', 'reviewer', 'auditTime', 'def1', 'def2', 'def3', 'def4', 'def5', 'def6', 'def7', 'def8', 'def9', 'def10', 'def11', 'def12', 'def13', 'def14', 'def15', 'def16']
//addField：新增循环使用
            def updateSql = " update YZ_CRM_Cost set "
            def kingSet = []
            datalist['modifier'] = mypara.set.user.name
            datalist['def1'] = "0"
            datalist['modifiedtime'] = sdf_draft.format(new Date())
            for (i in updateField) {
                if (datalist[i]) {
                    if (Date.class.isInstance(datalist[i])) {
                        sdf_draft.format(datalist[i])
                        kingSet.add(" ${i}='${sdf_draft.format(datalist[i])}'")
                    } else {
                        kingSet.add(" ${i}='${datalist[i]}'")
                    }
                } else {
                    if (i.equals('totalCost')) {
                        kingSet.add("${i}=0.00");
                        continue;
                    }
                    kingSet.add(" ${i}=null")
                }
            }
            updateSql = updateSql + kingSet.join(',') + "  where id ='${datalist['id']}'; "
            //费用单体==================================================
            def deleteSqlitem = "";
            if (itemtremoveList.size() > 0) {
                def idstr = "(0";
                for (int i = 0; i < itemtremoveList.size(); i++) {
                    idstr = idstr + "," + itemtremoveList.get(i).get("id");
                }
                idstr = idstr + ")";
                deleteSqlitem = "update YZ_CRM_Costs set vdef1=1 where id in " + idstr + "; "
            }
            def Sqlitems = "";
            for (int j = 0; j < itemlist.size(); j++) {
                if (itemlist.get(j)['nid'] != null) {
                    def updateFielditem = ['expenseType', 'purpose', 'remark', 'money', 'enclosure', 'vdef1', 'vdef2', 'vdef3', 'vdef4', 'vdef5', 'vdef6', 'vdef7', 'vdef8', 'vdef9', 'vdef10', 'vdef11', 'vdef12', 'vdef13', 'vdef14', 'vdef15', 'vdef16']
//addField：新增循环使用
                    def updateSqlitem = " update YZ_CRM_Costs set "
                    def kingSetitem = []
                    for (i in updateFielditem) {
                        if (itemlist.get(j)[i]) {
                            if (Date.class.isInstance(itemlist.get(j)[i])) {
                                sdf_draft.format(itemlist.get(j)[i])
                                kingSetitem.add(" ${i}='${sdf_draft.format(itemlist.get(j)[i])}'")
                            } else {
                                kingSetitem.add(" ${i}='${itemlist.get(j)[i]}'")
                            }
                        } else {
                            kingSetitem.add(" ${i}=null")
                        }
                    }
                    updateSqlitem = updateSqlitem + kingSetitem.join(',') + "  where id ='${itemlist.get(j)['id']}'; "
                    Sqlitems = Sqlitems + updateSqlitem;
                } else {
                    def addFielditem = ['id', 'nid', 'expenseType', 'purpose', 'remark', 'money', 'enclosure', 'vdef1', 'vdef2', 'vdef3', 'vdef4', 'vdef5', 'vdef6', 'vdef7', 'vdef8', 'vdef9', 'vdef10', 'vdef11', 'vdef12', 'vdef13', 'vdef14', 'vdef15', 'vdef16']
//addField：新增循环使用
                    itemlist.get(j)['nid'] = datalist.get("id")
                    def insertSqlitem = " insert into YZ_CRM_Costs  ( " + addFielditem.join(',') + ") values( "
                    def kingSetitem = []
                    //循环表头的创建list
                    //header表头创建map
                    for (i in addFielditem) {
                        if (itemlist.get(j)[i]) {//判断是否为空。
                            if (Date.class.isInstance(itemlist.get(j)[i])) { //判断是否是时间类型
                                sdf_draft.format(itemlist.get(j)[i])
                                kingSetitem.add("'${sdf_draft.format(itemlist.get(j)[i])}'")
                            } else {
                                kingSetitem.add("'${itemlist.get(j)[i]}'")
                            }
                        } else {
                            kingSetitem.add(null)
                        }
                    }
                    insertSqlitem = insertSqlitem + kingSetitem.join(',') + "); "
                    Sqlitems = Sqlitems + insertSqlitem;
                }
            }
            Sqlitems = Sqlitems + deleteSqlitem;
            updateSql = updateSql + Sqlitems
//            println("打印修改SQL语句" + updateSql)
            conn.execute(updateSql);
            //反写合同项目费用
            //查询合同中的项目费用
            def fyxmfy = conn.firstRow("select totalCost from YZ_CRM_Cost where id=" + datalist.get("id") + " ")
            def htxmfy = conn.firstRow("select projectfee from YZ_CRM_Contract where code='" + datalist.get("code") + "' ")
            if (htxmfy != null && htxmfy.projectfee != null) {

            } else {
                htxmfy.projectfee = 0
            }
            def jine = 0
            BigDecimal aa = new BigDecimal(fyxmfy.totalCost.toString())
            BigDecimal bb = new BigDecimal(htxmfy.projectfee.toString())
            jine = aa + bb
            conn.execute("update YZ_CRM_Contract set projectfee=" + jine + " where code='" + datalist.get("code") + "' ")
            conn.commit();
            mypara.msg('修改成功！')
        } catch (Exception e) {
            conn.rollback();
            e.printStackTrace()
            mypara.msg("修改失败！" + e)
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：费用单插入数据库方法。
     * 方法参数：
     * 返回值：
     * */

    def insertSQL() {
        def conn = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            conn.connection.autoCommit = false;
            def id = jdbcManager.getsqnid();//费用单主ID
            //费用单头====================================================
            def addField = ['id', 'htid', 'ccode', 'reportingTime', 'rycode', 'informant', 'departCode', 'department', 'constatus', 'reimbursementType', 'code', 'cusCode', 'cusName', 'totalCost', 'creatorCode', 'creator', 'creationtime', 'modifier', 'modifiedtime', 'reviewer', 'auditTime', 'def1', 'def2', 'def3', 'def4', 'def5', 'def6', 'def7', 'def8', 'def9', 'def10', 'def11', 'def12', 'def13', 'def14', 'def15', 'def16']
//addField：新增循环使用
            datalist['id'] = id
            datalist['def1'] = "0"
            datalist['constatus'] = "6"
            datalist['creatorCode'] = mypara.set.user.code
            datalist['creator'] = mypara.set.user.name
            datalist['creationtime'] = sdf_draft.format(new Date())
//            datalist['reportingTime'] = sdf_draft.format(new Date())
            datalist['def6'] = "未提交"
            def insertSql = " insert into YZ_CRM_Cost  ( " + addField.join(',') + ") values( "
            def kingSet = []
            //循环表头的创建list
            //header表头创建map
            for (i in addField) {
                if (datalist[i]) {//判断是否为空。
                    if (Date.class.isInstance(datalist[i])) { //判断是否是时间类型
                        sdf_draft.format(datalist[i])
                        kingSet.add("'${sdf_draft.format(datalist[i])}'")
                    } else {
                        kingSet.add("'${datalist[i]}'")
                    }
                } else {
                    if (i.equals('totalCost')) {
                        kingSet.add(0.00);
                        continue;
                    }
                    kingSet.add(null)
                }
            }
            insertSql = insertSql + kingSet.join(',') + "); "
            //费用表体==================================================
            def Sqlitems = "";
            for (int j = 0; j < itemlist.size(); j++) {
                def addFielditem = ['id', 'nid', 'expenseType', 'purpose', 'remark', 'money', 'enclosure', 'vdef1', 'vdef2', 'vdef3', 'vdef4', 'vdef5', 'vdef6', 'vdef7', 'vdef8', 'vdef9', 'vdef10', 'vdef11', 'vdef12', 'vdef13', 'vdef14', 'vdef15', 'vdef16']
//addField：新增循环使用
                itemlist.get(j)['nid'] = id
                //                itemlist.get(j)['vdef1']="0"
                def insertSqlitem = " insert into YZ_CRM_Costs  ( " + addFielditem.join(',') + ") values( "
                def kingSetitem = []
                //循环表头的创建list
                //header表头创建map
                for (i in addFielditem) {
                    if (itemlist.get(j)[i]) {//判断是否为空。
                        if (Date.class.isInstance(itemlist.get(j)[i])) { //判断是否是时间类型
                            sdf_draft.format(itemlist.get(j)[i])
                            kingSetitem.add("'${sdf_draft.format(itemlist.get(j)[i])}'")
                        } else {
                            kingSetitem.add("'${itemlist.get(j)[i]}'")
                        }
                    } else {
                        kingSetitem.add(null)
                    }
                }
                insertSqlitem = insertSqlitem + kingSetitem.join(',') + "); "
                Sqlitems = Sqlitems + insertSqlitem;
            }
            insertSql = insertSql + Sqlitems
//            println("打印插入SQL语句" + insertSql)
            conn.execute(insertSql);
            conn.commit();
            mypara.msg('保存成功！')
            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 6, mypara.set.user.code, mypara.set.user.code, "费用单发起", "费用单号:" + datalist.get("ccode"))
        } catch (Exception e) {
            conn.rollback();
            e.printStackTrace()
            mypara.msg("保存失败！" + e)
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-05-24
     * 方法作用：修改行金额改变表头回款金额。
     * 方法参数：
     * 返回值：
     * */

    def gb() {
        //循环表体金额并合计
        def heji = 0
        for (int i = 0; i < itemlist.size(); i++) {
            if (itemlist[i]['money'] != null && itemlist[i]['money'] != '') {
                BigDecimal hkje = new BigDecimal("" + itemlist[i]['money'] + "")
                heji = heji + hkje
            }
        }
        datalist['totalCost'] = heji
        org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:hkje");
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：费用单修改数据库方法。
     * 方法参数：
     * 返回值：
     * */

    def updateSQL() {
        def conn = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            conn.connection.autoCommit = false;
            //费用单头====================================================
            def updateField = ['htid', 'ccode', 'reportingTime', 'rycode', 'informant', 'departCode', 'department', 'constatus', 'reimbursementType', 'code', 'cusCode', 'cusName', 'totalCost', 'creator', 'creationtime', 'modifier', 'modifiedtime', 'reviewer', 'auditTime', 'def1', 'def2', 'def3', 'def4', 'def5', 'def6', 'def7', 'def8', 'def9', 'def10', 'def11', 'def12', 'def13', 'def14', 'def15', 'def16']
//addField：新增循环使用
            def updateSql = " update YZ_CRM_Cost set "
            def kingSet = []
            datalist['modifier'] = mypara.set.user.name
            datalist['def1'] = "0"
            datalist['modifiedtime'] = sdf_draft.format(new Date())
            for (i in updateField) {
                if (datalist[i]) {
                    if (Date.class.isInstance(datalist[i])) {
                        sdf_draft.format(datalist[i])
                        kingSet.add(" ${i}='${sdf_draft.format(datalist[i])}'")
                    } else {
                        kingSet.add(" ${i}='${datalist[i]}'")
                    }
                } else {
                    if (i.equals('totalCost')) {
                        kingSet.add("${i}=0.00");
                        continue;
                    }
                    kingSet.add(" ${i}=null")
                }
            }
            updateSql = updateSql + kingSet.join(',') + "  where id ='${datalist['id']}'; "
            //费用单体==================================================
            def deleteSqlitem = "";
            if (itemtremoveList.size() > 0) {
                def idstr = "(0";
                for (int i = 0; i < itemtremoveList.size(); i++) {
                    idstr = idstr + "," + itemtremoveList.get(i).get("id");
                }
                idstr = idstr + ")";
                deleteSqlitem = "update YZ_CRM_Costs set vdef1=1 where id in " + idstr + "; "
            }
            def Sqlitems = "";
            for (int j = 0; j < itemlist.size(); j++) {
                if (itemlist.get(j)['nid'] != null) {
                    def updateFielditem = ['expenseType', 'purpose', 'remark', 'money', 'enclosure', 'vdef1', 'vdef2', 'vdef3', 'vdef4', 'vdef5', 'vdef6', 'vdef7', 'vdef8', 'vdef9', 'vdef10', 'vdef11', 'vdef12', 'vdef13', 'vdef14', 'vdef15', 'vdef16']
//addField：新增循环使用
                    def updateSqlitem = " update YZ_CRM_Costs set "
                    def kingSetitem = []
                    for (i in updateFielditem) {
                        if (itemlist.get(j)[i]) {
                            if (Date.class.isInstance(itemlist.get(j)[i])) {
                                sdf_draft.format(itemlist.get(j)[i])
                                kingSetitem.add(" ${i}='${sdf_draft.format(itemlist.get(j)[i])}'")
                            } else {
                                kingSetitem.add(" ${i}='${itemlist.get(j)[i]}'")
                            }
                        } else {
                            kingSetitem.add(" ${i}=null")
                        }
                    }
                    updateSqlitem = updateSqlitem + kingSetitem.join(',') + "  where id ='${itemlist.get(j)['id']}'; "
                    Sqlitems = Sqlitems + updateSqlitem;
                } else {
                    def addFielditem = ['id', 'nid', 'expenseType', 'purpose', 'remark', 'money', 'enclosure', 'vdef1', 'vdef2', 'vdef3', 'vdef4', 'vdef5', 'vdef6', 'vdef7', 'vdef8', 'vdef9', 'vdef10', 'vdef11', 'vdef12', 'vdef13', 'vdef14', 'vdef15', 'vdef16']
//addField：新增循环使用
                    itemlist.get(j)['nid'] = datalist.get("id")
                    def insertSqlitem = " insert into YZ_CRM_Costs  ( " + addFielditem.join(',') + ") values( "
                    def kingSetitem = []
                    //循环表头的创建list
                    //header表头创建map
                    for (i in addFielditem) {
                        if (itemlist.get(j)[i]) {//判断是否为空。
                            if (Date.class.isInstance(itemlist.get(j)[i])) { //判断是否是时间类型
                                sdf_draft.format(itemlist.get(j)[i])
                                kingSetitem.add("'${sdf_draft.format(itemlist.get(j)[i])}'")
                            } else {
                                kingSetitem.add("'${itemlist.get(j)[i]}'")
                            }
                        } else {
                            kingSetitem.add(null)
                        }
                    }
                    insertSqlitem = insertSqlitem + kingSetitem.join(',') + "); "
                    Sqlitems = Sqlitems + insertSqlitem;
                }
            }
            Sqlitems = Sqlitems + deleteSqlitem;
            updateSql = updateSql + Sqlitems
//            println("打印修改SQL语句" + updateSql)
            conn.execute(updateSql);
            conn.commit();
            mypara.msg('修改成功！')
        } catch (Exception e) {
            conn.rollback();
            e.printStackTrace()
            mypara.msg("修改失败！" + e)
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：放弃保存方法。
     * 方法参数：
     * 返回值：
     * */

    def nosavefun() {
        lbif = true;
        butone = true;
        buttwo = false;
        baoxiao = true;
        ht = true;
        update = true;
        if (datalist.size() != 0 && datalist.get("id") != null && !datalist.get("id").toString().equals("")) {
            select(" and a.id=" + pageNumber + " ");
            dtxslx2()
        } else {
            def id = null;
            def conn = null;
            try {
                conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
                datalist = [id: null, htid: null, ccode: null, reportingTime: null, rycode: null, informant: null, departCode: null, department: null, constatus: null, code: null, cusCode: null, cusName: null, totalCost: null, reimbursementType: null, creatorCode: null, creator: null, creationtime: null, modifier: null, modifiedtime: null, reviewer: null, auditTime: null, def1: null, def2: null, def3: null, def4: null, def5: null, def6: null, def7: null, def8: null, def9: null, def10: null, def11: null, def12: null, def13: null, def14: null, def15: null, def16: null];
                itemlist = []
                filelist = []
                sczry = new singleczbean();//人员
                dtxslx2()
                java.text.SimpleDateFormat formatterid = new java.text.SimpleDateFormat("yyyyMMdd")
                def nowtimeid = formatterid.format(new Date())//当前时间
                //修改单据编号表
                def selectSql = "select id from [dbo].[Cost_TableId] where tName='" + nowtimeid + "'"
                def map = conn.firstRow(selectSql)
                id = map.get("id") - 1;
                def updateSql = "update Cost_TableId set id='" + id + "' where tName='" + nowtimeid + "'"
                conn.execute(updateSql);
                return id;
            } catch (Exception e) {
                e.printStackTrace()
                mypara.msg("查询失败！")
            } finally {
                if (conn != null) {
                    conn.close()
                }
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：修改按钮调用方法。
     * 方法参数：
     * 返回值：
     * */

    def updatefun() {
        if (datalist.size() != 0 && datalist.get("id") != null && !datalist.get("id").toString().equals("")) {
            lbif = false;
            butone = false;
            buttwo = true;
            //驳回按钮控制
            if (datalist.get("constatus") == 4) {
                baoxiao = false;
                ht = false;
            } else {
                baoxiao = true;
                ht = true;
            }
            xiugai = true;
            shanchu = true;
            qishen = true;
            tj = true;
            shenhe = true;
            fjyl = true;
            fjsc = true;
            update = true;
            itemtremoveList = [];
            files = []
            dtxslx()
        } else {
            mypara.msg('未选择修改数据。')
        }
    }
    /**
     * @author: 肖兴顺
     * @description: 合同前费用修改
     * @date: 2023-08-10 9:13
     * @param:
     * @return:
     * */
    def htqUpdate() {
        if (datalist.get("reimbursementType") == '4') {
            if (datalist.size() != 0 && datalist.get("id") != null && !datalist.get("id").toString().equals("")) {
                lbif = true;
                butone = false;
                buttwo = true;
                baoxiao = true;
                ht = false;
                xiugai = true;
                shanchu = true;
                qishen = true;
                tj = true;
                shenhe = true;
                fjyl = true;
                fjsc = true;
                update = true;
                itemtremoveList = [];
                files = []
            } else {
                mypara.msg('未选择修改的合同前费用。')
            }
        } else {
            mypara.msg('报销类型不是合同前费用无法修改。')
        }

    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-04
     * 方法作用：删除按钮调用方法。
     * 方法参数：
     * 返回值：
     * */

    def deletefun() {
        if (datalist.size() != 0 && datalist.get("id") != null && !datalist.get("id").toString().equals("")) {
            if (datalist.get("constatus") == 3 || datalist.get("constatus") == 1 || datalist.get("constatus") == 2) {
                mypara.msg('单据已生效无法删除！')
                return
            }
            def conn = null;
            try {
                conn = groovy.sql.Sql.newInstance(mypara.hqcon(2));
                conn.connection.autoCommit = false;
                def modifier = mypara.getSet().getUser().name//当前登录人
                java.text.SimpleDateFormat formatter3 = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                def CloseTime = formatter3.format(new Date())//时间
                conn.execute("update YZ_CRM_Cost set def1=1,modifier='" + modifier + "',modifiedtime='" + CloseTime + "' where id=" + datalist.get("id") + "  ")
                //删除审批记录
                conn.execute("delete from [wsdsjmcrm].[dbo].[wsd_spjl] where gnnid = '" + datalist.get("id") + "' and functioncode='expense'")
                //删除审批消息
                conn.execute("delete from [wsdsjmcrm].[dbo].[wsd_spmsg] where gnnid = '" + datalist.get("id") + "' and functioncode='expense'")
                conn.commit();
                mypara.msg('删除成功！')
            } catch (Exception e) {
                e.printStackTrace()
                conn.rollback();
                mypara.msg("删除失败！")
            } finally {
                if (conn != null) {
                    conn.close()
                }
            }
            select(" ")
        } else {
            mypara.msg('未选择删除数据！')
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-06
     * 方法作用：费用单弃审方法。
     * 方法参数：
     * 返回值：
     * */

    def qishen() {
        if (datalist.get("constatus") == 3) {
            def conn = null;
            try {
                conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
                def name = mypara.getSet().getUser().name//当前登录人
                java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                def time = formatter.format(new Date())//当前时间
                //项目费用
                if (datalist.get("reimbursementType") == '2') {
                    def fyxmfy = conn.firstRow("select totalCost from YZ_CRM_Cost where id=" + datalist.get("id") + " ")
                    def htxmfy = conn.firstRow("select projectfee from YZ_CRM_Contract where id=" + datalist.get("htid") + " ")
                    if (htxmfy.projectfee == null || htxmfy == null || htxmfy.projectfee == 0) {
                        htxmfy.projectfee = 0
                    }
                    def jine = 0
                    BigDecimal aa = new BigDecimal(fyxmfy.totalCost.toString())
                    BigDecimal bb = new BigDecimal(htxmfy.projectfee.toString())
                    if (htxmfy == null || htxmfy.projectfee == null || htxmfy.projectfee == 0) {
                        jine = null;
                    } else {
                        jine = bb - aa;
                    }
                    def deleteSql = "update YZ_CRM_Cost set constatus=6,auditTime=null,reviewer=null,def6 = '已驳回' where id=" + datalist.get("id") + "; "
                    deleteSql = deleteSql + "update YZ_CRM_Contract set projectfee=" + jine + " where id=" + datalist.get("htid") + " ; "
                    datalist.put("constatus", 6);
                    datalist.put("auditTime", null);
                    datalist.put("reviewer", null);
                    conn.execute(deleteSql);
                    //删除审批信息
                    conn.execute(" delete from [wsdsjmcrm]..wsd_spmsg where gnnid='" + datalist.get("id") + "' and functioncode='expense' and msgtype !=6 ")
                    //删除审批记录
                    conn.execute(" delete from [wsdsjmcrm]..wsd_spjl where gnnid = '" + datalist.get("id") + "' and functioncode='expense' ")
                    mypara.msg('弃审成功！')
                }
                //客户费用
                if (datalist.get("reimbursementType") == '3') {
                    def fyxmfy = conn.firstRow("select totalCost from YZ_CRM_Cost where id=" + datalist.get("id") + " ")
                    def htxmfy = conn.firstRow("select custfee from YZ_CRM_Contract where id=" + datalist.get("htid") + " ")
                    if (htxmfy.custfee == null || htxmfy == null || htxmfy.custfee == 0) {
                        htxmfy.custfee = 0
                    }
                    def jine = 0
                    BigDecimal aa = new BigDecimal(fyxmfy.totalCost.toString())
                    BigDecimal bb = new BigDecimal(htxmfy.custfee.toString())
                    if (htxmfy == null || htxmfy.custfee == null || htxmfy.custfee == 0) {
                        jine = null;
                    } else {
                        jine = bb - aa
                    }
                    def deleteSql = "update YZ_CRM_Cost set constatus=6,auditTime=null,reviewer=null,def6 = '已驳回' where id=" + datalist.get("id") + "; "
                    deleteSql = deleteSql + "update YZ_CRM_Contract set custfee=" + jine + " where id=" + datalist.get("htid") + "; "
                    datalist.put("constatus", 6);
                    datalist.put("auditTime", null);
                    datalist.put("reviewer", null);
                    conn.execute(deleteSql);
                    //删除审批信息
                    conn.execute(" delete from [wsdsjmcrm]..wsd_spmsg where gnnid='" + datalist.get("id") + "' and functioncode='expense' and msgtype !=6 ")
                    //删除审批记录
                    conn.execute(" delete from [wsdsjmcrm]..wsd_spjl where gnnid = '" + datalist.get("id") + "' and functioncode='expense' ")
                    mypara.msg('弃审成功！')
                }
                //公共费用
                if (datalist.get("reimbursementType") == '1') {
                    def deleteSql = "update YZ_CRM_Cost set constatus=6,auditTime=null,reviewer=null,def6 = '已驳回' where id=" + datalist.get("id") + "; "
                    datalist.put("constatus", 6);
                    datalist.put("auditTime", null);
                    datalist.put("reviewer", null);
                    conn.execute(deleteSql);
                    //删除审批信息
                    conn.execute(" delete from [wsdsjmcrm]..wsd_spmsg where gnnid='" + datalist.get("id") + "' and functioncode='expense' and msgtype !=6 ")
                    //删除审批记录
                    conn.execute(" delete from [wsdsjmcrm]..wsd_spjl where gnnid = '" + datalist.get("id") + "' and functioncode='expense' ")
                    mypara.msg('弃审成功！')
                }
                //合同前费用
                if (datalist.get("reimbursementType") == '4') {
                    if (datalist.get("code").equals("") || datalist.get("code") == null) {
                        def deleteSql = "update YZ_CRM_Cost set constatus=6,auditTime=null,reviewer=null,def6 = '已驳回' where id=" + datalist.get("id") + "; "
                        datalist.put("constatus", 6);
                        datalist.put("auditTime", null);
                        datalist.put("reviewer", null);
                        conn.execute(deleteSql);
                        //删除审批信息
                        conn.execute(" delete from [wsdsjmcrm]..wsd_spmsg where gnnid='" + datalist.get("id") + "' and functioncode='expense' and msgtype !=6 ")
                        //删除审批记录
                        conn.execute(" delete from [wsdsjmcrm]..wsd_spjl where gnnid = '" + datalist.get("id") + "' and functioncode='expense' ")
                        mypara.msg('弃审成功！')
                    } else {
                        def fyxmfy = conn.firstRow("select totalCost from YZ_CRM_Cost where id=" + datalist.get("id") + " ")
                        def htxmfy = conn.firstRow("select projectfee from YZ_CRM_Contract where id=" + datalist.get("htid") + " ")
                        if (htxmfy.projectfee == null || htxmfy == null || htxmfy.projectfee == 0) {
                            htxmfy.projectfee = 0
                        }
                        def jine = 0
                        BigDecimal aa = new BigDecimal(fyxmfy.totalCost.toString())
                        BigDecimal bb = new BigDecimal(htxmfy.projectfee.toString())
                        if (htxmfy == null || htxmfy.projectfee == null || htxmfy.projectfee == 0) {
                            jine = null;
                        } else {
                            jine = bb - aa;
                        }
                        def deleteSql = "update YZ_CRM_Cost set constatus=6,auditTime=null,reviewer=null,def6 = '已驳回' where id=" + datalist.get("id") + "; "
                        deleteSql = deleteSql + "update YZ_CRM_Contract set projectfee=" + jine + " where id=" + datalist.get("htid") + " ; "
                        datalist.put("constatus", 6);
                        datalist.put("auditTime", null);
                        datalist.put("reviewer", null);
                        conn.execute(deleteSql);
                        //删除审批信息
                        conn.execute(" delete from [wsdsjmcrm]..wsd_spmsg where gnnid='" + datalist.get("id") + "' and functioncode='expense' and msgtype !=6 ")
                        //删除审批记录
                        conn.execute(" delete from [wsdsjmcrm]..wsd_spjl where gnnid = '" + datalist.get("id") + "' and functioncode='expense' ")
                        mypara.msg('弃审成功！')
                    }
                }
            } catch (Exception e) {
                e.printStackTrace()
                mypara.msg("弃审失败！")
            } finally {
                if (conn != null) {
                    conn.close()
                }
            }
        } else {
            mypara.msg('单据未生效，弃审失败！')
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-06-27
     * 方法作用：按钮控制方法。
     * 方法参数：
     * 返回值：
     * */
    def pageNumber

    def buttonControl() {
        def conn = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(1));
            def selUser = conn.firstRow("select spusercode from wsd_spjl where functioncode='expense' and gnnid=" + datalist.get("id") + " order by arriveddate desc ");
            if (filelist == []) {
                fjyl = true;
            } else {
                fjyl = false;
            }
            sczry = new singleczbean();//人员
            sczry.setNid(datalist.get("rycode").toString());
            sczry.setName(datalist.get("informant"));
            pageNumber = datalist.get("id");
            ht = true;
            //提交按钮控制
            if (datalist.get("constatus") == 1) {
                lbif = true;
                buttwo = false;
                xiugai = false;
                shanchu = true;
                shenhe = false;
                qishen = true;
                butone = true;
                tj = true;
                fjsc = false;
            }
            //审批中按钮控制
            if (datalist.get("constatus") == 2) {
                lbif = true;
                buttwo = false;
                if (mypara.getSet().getUser().code.equals(selUser.spusercode)) {
                    xiugai = false;
                } else {
                    xiugai = true;
                }
                shanchu = true;
                shenhe = false;
                qishen = true;
                butone = true;
                tj = true;
                fjsc = true;
            }
            //生效按钮控制
            if (datalist.get("constatus") == 3) {
                //当为合同前费用并且是生效态时可以修改弃审
                if (datalist.get("reimbursementType") == '4') {
                    update = false;
                } else {
                    update = true;
                }
                lbif = true;
                buttwo = false;
                if (mypara.getSet().getUser().code.equals(selUser.spusercode)) {
                    xiugai = false;
                    qishen = false;
                } else {
                    xiugai = true;
                    qishen = true;
                }
                shanchu = true;
                shenhe = false;
                butone = true;
                tj = true;
                fjsc = true;
            }
            //驳回按钮控制
            if (datalist.get("constatus") == 4) {
                lbif = true;
                buttwo = false;
                xiugai = false;
                shanchu = false;
                shenhe = false;
                qishen = true;
                butone = true;
                tj = false;
                fjsc = false;
            }
            //开立按钮控制
            if (datalist.get("constatus") == 6) {
                lbif = true;
                buttwo = false;
                xiugai = false;
                shanchu = false;
                shenhe = false;
                qishen = true;
                butone = true;
                tj = false;
                fjsc = false;
            }
        } catch (Exception e) {
            e.printStackTrace()
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /**
     * @Author: 肖兴顺
     * @Description: 首页
     * @Date: 2022-07-20 9:15
     * @Param:
     * @return:
     * */
    def index

    def shouyefun() {
        //调用查询方法
        select(" ")
        if (list == []) {
            mypara.msg("无数据")
            return
        }
        index = 0
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-06
     * 方法作用：上一页按钮调用方法。
     * 方法参数：
     * 返回值：
     * */

    def prepagefun() {
        if (index == null) {
            shouyefun()
        } else {
            if (index == 0) {
                mypara.msg("无数据！")
                return
            } else {
                datalist = list.get(index - 1)//表头集合
                itemlist = list.get(index - 1).get("item")//表体查询集合
                filelist = list.get(index - 1).get("file")//附件查询集合
                index = index - 1
                buttonControl()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-06
     * 方法作用：下一页按钮调用方法。
     * 方法参数：
     * 返回值：
     * */

    def nextpagefun() {
        if (index == null) {
            shouyefun()
        } else {
            if (index == list.size() - 1) {
                mypara.msg("无数据")
                return
            } else {
                datalist = list.get(index + 1)//表头集合
                itemlist = list.get(index + 1).get("item")//表体查询集合
                filelist = list.get(index + 1).get("file")//附件查询集合
                index = index + 1
                buttonControl()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-06
     * 方法作用：末页按钮调用方法。
     * 方法参数：
     * 返回值：
     * */

    def lastpagefun() {
        select("")
        if (list == []) {
            mypara.msg("无数据")
            return
        }
        datalist = list.get(list.size() - 1)//表头集合
        itemlist = list.get(list.size() - 1).get("item")//表体查询集合
        filelist = list.get(list.size() - 1).get("file")//附件查询集合
        index = list.size() - 1
        buttonControl()
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-07
     * 方法作用：跳转接收。
     * 方法参数：
     * 返回值：
     * */

    def csh() {
        def expenselistForm = false;//接收是否跳转
        def fyid = false;//接收合同id
        def selectExpense = false;//接收是否查询
        //审批跳转
        if (FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("gnid") != null) {
            def fynid = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("gnid");
            select(" and a.id=" + fynid + " ");
        }
        if (javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().containsKey("expenselistForm")) {
            expenselistForm = javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("expenselistForm")
        }
        if (javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().containsKey("fyid")) {
            fyid = javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("fyid")
        }
        if (javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().containsKey("selectExpense")) {
            selectExpense = javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("selectExpense")
        }
        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().remove("expenselistForm")
//清除session
        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().remove("fyid")
//清除session
        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().remove("selectExpense")
//清除session
        if (expenselistForm) {
            if (fyid) {
                select(" and a.id=" + fyid + " ");
                if (!selectExpense) {
                    updatefun()
                }
            } else {
                insertfun()
            }
        }
        return null;
    }

    def insertanfun() {
        lbif = true;
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-08
     * 方法作用：根据选择的报销类型动态显示费用类型。
     * 方法参数：
     * 返回值：
     * */
    def ht = true;

    def dtxslx() {
        xzlist = []
        def conn = null;
        def sql = "";
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2));
            sql = "select id,name from YZ_CRM_ExpenseType where def2=" + datalist.get("reimbursementType") + ""
            xzlist = conn.rows(sql);
            if (datalist.get("reimbursementType") == '4' || datalist.get("reimbursementType") == '1') {
                ht = true;
            } else {
                ht = false;
            }
            org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:ht");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-08
     * 方法作用：查询所有费用类型。
     * 方法参数：
     * 返回值：
     * */

    def dtxslx2() {
        xzlist = []
        def conn = null;
        def sql = "";
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2));
            sql = "select id,name from YZ_CRM_ExpenseType "
            xzlist = conn.rows(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-16
     * 方法作用：根据查询条件跳转到费用单列表。
     * 方法参数：
     * 返回值：
     * */
//    def khmc
//    def tbr
//    def tbbm
//    def bxlx

    def select() {
        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("zt", "zt");
//        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("tbr", tbr);
//        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("tbbm", tbbm);
//        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("bxlx", bxlx);
//        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("khmc", khmc);
        jdbcManager.navikzbd("/ext/expenselist.xhtml", "费用单列表", "expenselist");
        javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap().remove("zt")
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-08
     * 方法作用：上传文件、图片
     * 方法参数：FileUploadEvent
     * 返回值：
     * */
    def files = []

    def upfile2(FileUploadEvent f) {
        SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy-MM-dd")
        def conn = null
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            UploadedFile file1 = f.getFile()//文件
            InputStream stream = file1.getInputStream()
            def filename = file1.getFileName()                      //获取pdf文件字节流
            def pdf = [filename, stream]
            File[] list = new File("D:/fywj/").listFiles();
            def count = 0
            def file2
            for (File file : list) {
                if (file.isDirectory()) {
                    count++
                    file2 = file.getName()
                }
            }
            def date = new Date()
            def date2 = formatter2.format(date)
            if (count == 0) {
                File file3 = new File("D:/fywj/" + date2)
                file3.mkdir()
                file2 = date2
            }
            if (date.getTime() - formatter2.parse(file2).getTime() >= 30) {
                File file3 = new File("D:/fywj/" + date2)
                file3.mkdir()
                file2 = date2
            }
            def pdfpath
            if (pdf != []) {
                pdfpath = "D:/fywj/" + file2 + "/" + pdf[0]
                File ff = new File(pdfpath)
                ff.createNewFile()
                ff.append(pdf[1])
            }
            conn.execute "insert into [dbo].[YZ_CRM_file](filename,filepath,creator,creatorname,creationtime,fyid)values('" + filename + "','" + pdfpath + "','" + mypara.set.user.code + "','" + mypara.set.user.name + "',GETDATE(),'" + datalist.get("id") + "')"
            mypara.msg("上传成功")
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
        wjcx()
        select(" and a.id=" + datalist.get("id") + " ");
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-05-25
     * 方法作用：上传文件查询
     * 方法参数：FileUploadEvent
     * 返回值：
     * */
    def wjselectrow
    def wjlist = []

    def wjcx() {
        rs2 = true
        def conn = null
        wjlist = []
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2));
            def chaxun = "select id,filename from [dbo].[YZ_CRM_file] where fyid=" + datalist.get("id") + ""
            conn.eachRow(chaxun) {
                def t = ["id": it.id, "filename": it.filename]
                wjlist.add(t)
            }
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-05-25
     * 方法作用：附件删除
     * 方法参数：传入文档id
     * 返回值：
     * */

    def wdshanchu(def aa) {
        def conn = null;
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2));
            //查询本地文件路径
            def select = conn.firstRow(" select filepath from [dbo].[YZ_CRM_file] where id=" + aa.id + " ")
            conn.execute "delete from [dbo].[YZ_CRM_file] where id=" + aa.id + ""
            mypara.msg("附件删除成功!")
            deleteFiles(select.filepath);//删除本地文件
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
        wjcx()
        select(" and a.id=" + datalist.get("id") + " ");
    }
    /**
     * @Author: 肖兴顺
     * @Description: 删除单个文件
     * @Date: 2022/12/1 15:29
     * @Param pathName 删除文件路径名
     * @Return
     *                 */
    public boolean deleteFiles(String pathName) {
        boolean flag = false;
        //根据路径创建文件对象
        File file = new File(pathName);
        //路径是个文件且不为空时删除文件
        if (file.isFile() && file.exists()) {
            flag = file.delete();
        }
        //删除失败时，返回false
        return flag;
    }
    //文件上传窗口关闭
    def rs2

    def GBrs1() {
        rs2 = false
    }

    def yulanclose() {           //文件预览窗口关闭
        ylrs = false
        ylfile = false;
        files.clear();
    }

    def yulan() {   //文件预览窗口打开
        if (datalist.size() != 0 && datalist.get("id") != null && !datalist.get("id").toString().equals("")) {
            if (filelist.size() == 0) {
                mypara.msg("没有附件")
                return
            } else {
                ylrs = true
            }
        } else {
            mypara.msg("未选择数据")
        }
    }

    def wenjianyulanxs() {
        def houzhui = fjxz["filepath"].split("\\.")[-1]
        if (houzhui.equals("pdf") || houzhui.equals("PDF")) {
            yulan("pdf")
            //打开对画框
            PrimeFaces.current().executeScript("PF('pdf').show()");
        } else if (houzhui.equals("jpg") || houzhui.equals("JPG") || houzhui.equals("png") || houzhui.equals("PNG") || houzhui.equals("jpeg") || houzhui.equals("JPEG")) {
            yulan(houzhui)
        } else {
            mypara.msg("非可预览格式,请下载")
            return
        }
        ylrs = true
    }
    /*
     * @Author: 肖兴顺
     * @Description: 文件下载
     * @Date: 2022/11/18 13:48
     * @Param
     * @Return
     **/

    def downLoad() {
        if (fjxz == null || fjxz.equals("")) {
            mypara.msg("请选择一行进行下载")
            return
        } else {
            return DefaultStreamedContent.builder().contentType("image/jpeg").name("" + fjxz["filename"] + "")
                    .stream(new SerializableSupplier<InputStream>() {
                        @Override
                        public InputStream get() {
                            try {
                                return new FileInputStream(new File("" + fjxz["filepath"] + ""));
                            } catch (IOException ex) {
                                Logger.getLogger(sysedit.class.getName()).log(Level.SEVERE, null, ex);
                                return null;
                            }
                        }
                    }).build();
        }
    }
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd")

    def yulan(def leixing) {
        if (leixing.equals("pdf")) {
            def aaa = new File(fjxz["filepath"])
            def bbb = fjxz["filepath"].split("/")
            def date = new Date()
            def date2 = formatter.format(date)
            File directory = new File("");// 参数为空
            String courseFile = directory.getCanonicalPath();
            courseFile = courseFile.substring(0, courseFile.length() - 3)
            def path = courseFile + "\\webapps\\wsdsjmcloud\\pdfyulan"
            def aa = true
            def ff = new File(path)
            for (File f : ff.listFiles()) {
                if ((date.getTime() - formatter.parse(f.getName()).getTime()) >= (3600 * 24 * 1000)) {
                    method(f)
                } else {
                    aa = false
                    continue;
                }
            }
            path = path + "\\" + date2
            if (aa) {
                def fff = new File(path)
                fff.mkdirs();
                fff.setWritable(true, false)
            }
            BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(new File(path + "\\" + bbb[-1])));
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            // 将pdf文件先加水印然后输出
            setWatermark(bos, fjxz["filepath"], "", "", "", 16);
            ylrs = true
            ylfile = ".\\pdfyulan\\" + date2 + "\\" + bbb[-1] + ""
        } else {
            def aaa = new File(fjxz["filepath"])
            def bbb = fjxz["filepath"].split('/')
            def date = new Date()
            def date2 = formatter.format(date)
            File directory = new File("");// 参数为空
            String courseFile = directory.getCanonicalPath();
            courseFile = courseFile.substring(0, courseFile.length() - 3)
            def path = courseFile + "\\webapps\\wsdsjmcloud\\pdfyulan"
            def aa = true
            def ff = new File(path)
            for (File f : ff.listFiles()) {
                if ((date.getTime() - formatter.parse(f.getName()).getTime()) >= (3600 * 24 * 1000)) {
                    method(f)
                } else {
                    aa = false
                    continue;
                }
            }
            path = path + "\\" + date2
            if (aa) {
                def fff = new File(path)
                fff.mkdirs();
                fff.setWritable(true, false)
            }
            copyFile(fjxz["filepath"], path + "\\" + bbb[-1])
            ylrs = true
            ylfile = ".\\pdfyulan\\" + date2 + "\\" + bbb[-1] + ""
        }
    }

    def copyFile(String oldPath, String newPath) {
        try {
            int bytesum = 0;
            int byteread = 0;
            File oldfile = new File(oldPath);
            if (oldfile.exists()) { //文件存在时
                InputStream inStream = new FileInputStream(oldPath); //读入原文件
                FileOutputStream fs = new FileOutputStream(newPath);
                byte[] buffer = new byte[1444];
                int length;
                while ((byteread = inStream.read(buffer)) != -1) {
                    bytesum += byteread; //字节数 文件大小
                    //System.out.println(bytesum);
                    fs.write(buffer, 0, byteread);
                }
                inStream.close();
            }
        } catch (Exception e) {
            System.out.println("复制单个文件操作出错");
            e.printStackTrace();
        }
    }

    def method(File f) {             //删除文件
        //首先判断f是文件还是文件夹
        if (f.isDirectory()) {
            //创建文件数组对象
            File[] files = f.listFiles();
            //开始遍历
            for (File key : files) {
                //如果是文件，就直接删除
                if (key.isFile()) {
                    key.delete();
                } else {//如果key是文件夹，就递归
                    method(key);//
                }
            }
        }
        //最后删除这个文件夹就可以了
        f.delete();
    }

    def getFile(byte[] bfile, String filePath, String fileName) {    //将字节流输入到tomcat中项目的文件夹下
        BufferedOutputStream bos = null;
        FileOutputStream fos = null;
        File file = null;
        try {
            File dir = new File(filePath);
            if (!dir.exists() && dir.isDirectory()) {//判断文件目录是否存在
                dir.mkdirs();
            }
            file = new File(filePath + "\\" + fileName);
            fos = new FileOutputStream(file);
            bos = new BufferedOutputStream(fos);
            bos.write(bfile);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
    //bos 预览文件      input 源文件        Time 上传时间       waterMarkName  设计人       auditor  审核人        permission 权限
    def setWatermark(BufferedOutputStream bos, String input, String Time, String waterMarkName, String auditor, int permission)
            throws DocumentException, IOException {
        PdfReader reader = new PdfReader(input);
        PdfStamper stamper = new PdfStamper(reader, bos);
        int total = reader.getNumberOfPages() + 1;
        PdfContentByte content;
        BaseFont base = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.EMBEDDED);
        PdfGState gs = new PdfGState();
        for (int i = 1; i < total; i++) {
            content = stamper.getOverContent(i);// 在内容上方加水印
            // content = stamper.getUnderContent(i);//在内容下方加水印
            gs.setFillOpacity(0.2f);
            // content.setGState(gs);
            content.beginText();
            content.setColorFill(Color.LIGHT_GRAY);//RED
            content.setFontAndSize(base, 22);
            content.setTextMatrix(70, 200);
            //            content.showTextAligned(Element.ALIGN_CENTER, "星辉数控", 505, 95, 45);
            //            Image image = Image.getInstance("D:/aa.jpeg");
            /*
            img.setAlignment(Image.LEFT | Image.TEXTWRAP);
            img.setBorder(Image.BOX); img.setBorderWidth(10);
            img.setBorderColor(BaseColor.WHITE); img.scaleToFit(100072);//大小
            img.setRotationDegrees(-30);//旋转
             */
            //            image.setAbsolutePosition(200, 206); // set the first background
            // image of the absolute
            //            image.scaleToFit(200, 200);
            //            content.addImage(image);
            content.setColorFill(Color.LIGHT_GRAY);
            content.setFontAndSize(base, 12);
            //            content.showTextAligned(Element.ALIGN_CENTER, "上传时间：" + Time + "", 525, 80, 45);
            //            content.showTextAligned(Element.ALIGN_CENTER, "设计人：" + waterMarkName + "", 545, 65, 45);
            //            content.showTextAligned(Element.ALIGN_CENTER, "审核人：" + auditor + "", 565, 50, 45);
            //            waterMarkName
            content.endText();

        }
        stamper.close();
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-15
     * 方法作用：刷新
     * 方法参数：
     * 返回值：
     * */

    def shuaxin() {
        select(" and a.id=" + pageNumber + " ");
    }
    def rs1//打开客户参照对话框
    def czhtbt = []//参照合同表头list
    def czhtbtrow //参照合同表头一行
    def gvjhlist = [] //参照过滤集合
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-25
     * 方法作用：根据客户查询出所有相关的合同
     * 方法参数：
     * 返回值：
     * */

    def khczht() {
        def conn = null
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            rs1 = true
            czhtbt = []
            def htbt = "select a.id,code,name,CONVERT(varchar(23),signingDate,23)as signingDate,b.id as khid,b.cCusName,cont_type from [dbo].[YZ_CRM_Contract] a left join [dbo].[YZ_CRM_Customer] b on a.cid=b.ID where constatus=3"
            conn.eachRow(htbt) {
                def leixing
                if (it.cont_type == 1 || it.cont_type == '1') {
                    leixing = '新购'
                } else if (it.cont_type == 2 || it.cont_type == '2') {
                    leixing = '加购'
                } else if (it.cont_type == 3 || it.cont_type == '3') {
                    leixing = '升级'
                } else if (it.cont_type == 4 || it.cont_type == '4') {
                    leixing = '升迁'
                } else if (it.cont_type == 5 || it.cont_type == '5') {
                    leixing = '服务费'
                } else if (it.cont_type == 6 || it.cont_type == '6') {
                    leixing = '实施'
                } else if (it.cont_type == 7 || it.cont_type == '7') {
                    leixing = '研发'
                } else if (it.cont_type == 8 || it.cont_type == '8') {
                    leixing = '开发外包'
                } else if (it.cont_type == 9 || it.cont_type == '9') {
                    leixing = '实施外包'
                } else if (it.cont_type == 10 || it.cont_type == '10') {
                    leixing = '服务外包'
                } else if (it.cont_type == 11 || it.cont_type == '11') {
                    leixing = '配套'
                }
                def t = ["id": it.id, "code": it.code, "name": it.name, "qdrq": it.signingDate, "khid": it.khid, "khmc": it.cCusName, "htlx": leixing]
                czhtbt.add(t)
            }
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-25
     * 方法作用：关闭客户参照
     * 方法参数：
     * 返回值：
     * */

    def closekh() {
        rs1 = false
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-25
     * 方法作用：客户参照确认
     * 方法参数：
     * 返回值：
     * */

    def queren() {
        if (czhtbtrow == null) {
            mypara.msg("请选择数据源")
            return;
        }
        def conn = null;
        try {
            czhtbt = []
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def htbt = "select a.id,code,name,CONVERT(varchar(23),signingDate,23)as signingDate,b.id as khid,b.cCusName,cont_type from [dbo].[YZ_CRM_Contract] a left join [dbo].[YZ_CRM_Customer] b on a.cid=b.ID where constatus=3"
            conn.eachRow(htbt) {
                def t = ["id": it.id, "code": it.code, "name": it.name, "khid": it.khid, "khmc": it.cCusName]
                czhtbt.add(t)
            }
            datalist['htid'] = czhtbtrow.id
            datalist['code'] = czhtbtrow.code
            datalist['cusCode'] = czhtbtrow.khid
            datalist['cusName'] = czhtbtrow.khmc
            rs1 = false
            org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:aa");
            org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:bb");
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-25
     * 方法作用：双击选择客户信息
     * 方法参数：
     * 返回值：
     * */

    def sjxzkh() {
        def conn = null
        try {
            czhtbt = []
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def htbt = "select a.id,code,name,CONVERT(varchar(23),signingDate,23)as signingDate,b.id as khid,b.cCusName,cont_type from [dbo].[YZ_CRM_Customer] b left join [dbo].[YZ_CRM_Contract] a on a.cid=b.ID where constatus=3 and a.id=" + czhtbtrow.id + ""
            conn.eachRow(htbt) {
                def t = ["id": it.id, "code": it.code, "name": it.name, "khid": it.khid, "khmc": it.cCusName]
                czhtbt.add(t)
            }
            datalist['htid'] = czhtbtrow.id
            datalist['code'] = czhtbtrow.code
            datalist['cusCode'] = czhtbtrow.khid
            datalist['cusName'] = czhtbtrow.khmc
            rs1 = false
            org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:aa");
            org.primefaces.PrimeFaces.current().ajax().update("maintab:expenseForm:bb");
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-25
     * 方法作用：客户自动完成
     * 方法参数：
     * 返回值：
     * */

    def selectkh(str) {
        def conn = null
        def arr = []
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def chaxun = "select cCusName from [dbo].[YZ_CRM_Customer] where cCusName like '%" + str + "%'"
            conn.eachRow(chaxun) {
                arr = arr + it.cCusName
            }
            return arr
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-02-15
     * 方法作用：客户自动完成选择获取id
     * 方法参数：
     * 返回值：
     * */

    def hqkhid() {
        def conn = null;
        def sql = "";
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2));
            sql = conn.firstRow("select id from [dbo].[YZ_CRM_Customer] where cCusName= '" + datalist.get("cusName") + "'")
            datalist['cusCode'] = sql.id
        } catch (Exception e) {
            println e.toString()
        } finally {
            if (null != conn) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-29
     * 方法作用：审批提交
     * 方法参数：
     * 返回值：
     * */

    def sptj() {
        def conn = null
        def qunxian = [:]
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            //判断当前提交人是否于费用单创建人一致
            def cjr = conn.firstRow("select creatorCode from [dbo].[YZ_CRM_Cost] where ID=" + datalist.get("id") + " ")
            //判断当前提交的是什么报销类型的费用单
            def bxlx = conn.firstRow("select reimbursementType from [dbo].[YZ_CRM_Cost] where ID=" + datalist.get("id") + "")
            if (cjr.creatorCode == mypara.set.user.code) {
                //报销类型-公共费用
                if (bxlx.reimbursementType == '1') {
                    //如果报销类型是公共费用（侯震）
                    if (bxlx.reimbursementType == '1' && (cjr.creatorCode == "hz" || cjr.creatorCode.equals("hz"))) {
                        def cx = conn.firstRow("select totalCost from YZ_CRM_Cost where isnull(def1,0)!=1 and id=" + datalist.get("id") + " ")
                        qunxian = ["fy001": '公共费用', "fy002": '侯震', "fy003": cx.totalCost]
                    } else if (bxlx.reimbursementType == '1' && (cjr.creatorCode == "zl" || cjr.creatorCode.equals("zl"))) {
                        qunxian = ["fy001": '公共费用', "fy002": '赵林', "fy003": 0]
                    } else if (bxlx.reimbursementType == '1' && (cjr.creatorCode == "aq" || cjr.creatorCode.equals("aq"))) {
                        qunxian = ["fy001": '公共费用', "fy002": '部门经理', "fy003": 0]
                    } else {
                        //根据创建人查询部门档案是部门经理还是普通员工
                        def ryid = conn.firstRow("select d.def3 from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.id=d.def3 where p.def3='" + cjr.creatorCode + "'")
                        //如果为空说明是普通员工
                        if ((ryid == null || ryid.def3 == null) && bxlx.reimbursementType == '1') {
                            //查询当前人的所属部门
                            def ssbm = conn.firstRow("select d.name from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.dept_id=d.id where p.def3='" + cjr.creatorCode + "'")
                            //判断提报人部门
                            if (bxlx.reimbursementType == '1' && ssbm.name.equals("中高端事业部")) {
                                //公共费用（普通员工）
                                qunxian = ["fy001": '公共费用', "fy002": '中高端事业部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '1' && ssbm.name.equals("客户成功部")) {
                                //公共费用（普通员工）
                                qunxian = ["fy001": '公共费用', "fy002": '客户成功部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '1' && ssbm.name.equals("研发中心")) {
                                //公共费用（普通员工）
                                qunxian = ["fy001": '公共费用', "fy002": '研发中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '1' && ssbm.name.equals("资源中心")) {
                                //公共费用（普通员工）
                                qunxian = ["fy001": '公共费用', "fy002": '资源中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '1' && ssbm.name.equals("商务财务部")) {
                                //公共费用（普通员工）
                                qunxian = ["fy001": '公共费用', "fy002": '商务财务部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '1' && ssbm.name.equals("品控部")) {
                                //公共费用（普通员工）
                                qunxian = ["fy001": '公共费用', "fy002": '品控部', "fy003": 0]
                            }
                        } else {
                            //公共费用（部门经理）
                            qunxian = ["fy001": '公共费用', "fy002": '部门经理', "fy003": 0]
                        }
                    }
                } else if (bxlx.reimbursementType == '4') {//报销类型-合同前费用
                    if (bxlx.reimbursementType == '4' && (cjr.creatorCode == "hz" || cjr.creatorCode.equals("hz"))) {
//如果报销类型是合同前费用（侯震)
                        qunxian = ["fy001": '合同前费用', "fy002": '侯震', "fy003": 0]
                    } else if (bxlx.reimbursementType == '4' && (cjr.creatorCode == "zl" || cjr.creatorCode.equals("zl"))) {
//如果报销类型是合同前费用（赵林)
                        qunxian = ["fy001": '合同前费用', "fy002": '赵林', "fy003": 0]
                    } else if (bxlx.reimbursementType == '4' && (cjr.creatorCode == "aq" || cjr.creatorCode.equals("aq"))) {
//如果报销类型是合同前费用（安桥)
                        qunxian = ["fy001": '合同前费用', "fy002": '部门经理', "fy003": 0]
                    } else {
                        //根据创建人查询部门档案是部门经理还是普通员工
                        def ryid = conn.firstRow("select d.def3 from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.id=d.def3 where p.def3='" + cjr.creatorCode + "'")
                        //如果为空说明是普通员工
                        if ((ryid == null || ryid.def3 == null) && bxlx.reimbursementType == '4') {
                            //查询当前人的所属部门
                            def ssbm = conn.firstRow("select d.name from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.dept_id=d.id where p.def3='" + cjr.creatorCode + "'")
                            //判断提报人部门
                            if (bxlx.reimbursementType == '4' && ssbm.name.equals("中高端事业部")) {
                                //合同前费用（普通员工）
                                qunxian = ["fy001": '合同前费用', "fy002": '中高端事业部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '4' && ssbm.name.equals("客户成功部")) {
                                //合同前费用（普通员工）
                                qunxian = ["fy001": '合同前费用', "fy002": '客户成功部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '4' && ssbm.name.equals("研发中心")) {
                                //合同前费用（普通员工）
                                qunxian = ["fy001": '合同前费用', "fy002": '研发中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '4' && ssbm.name.equals("资源中心")) {
                                //合同前费用（普通员工）
                                qunxian = ["fy001": '合同前费用', "fy002": '资源中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '4' && ssbm.name.equals("商务财务部")) {
                                //合同前费用（普通员工）
                                qunxian = ["fy001": '合同前费用', "fy002": '商务财务部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '4' && ssbm.name.equals("品控部")) {
                                //合同前费用（普通员工）
                                qunxian = ["fy001": '合同前费用', "fy002": '品控部', "fy003": 0]
                            }
                        } else {
                            //合同前费用（部门经理）
                            qunxian = ["fy001": '合同前费用', "fy002": '部门经理', "fy003": 0]
                        }
                    }
                } else if (bxlx.reimbursementType == '3') {//报销类型-客户费用
                    if (bxlx.reimbursementType == '3' && (cjr.creatorCode == "hz" || cjr.creatorCode.equals("hz"))) {
                        def cx = conn.firstRow("select id,htid,ccode,reportingTime,rycode,informant,departCode,department,constatus,code,cusCode,cusName,totalCost,reimbursementType,def1,def2,def3,def4,def5,def6,def7,def8,def9,def10,def11,def12,def13,def14,def15,def16,creatorCode,creator,creationtime,modifier,modifiedtime,reviewer,CONVERT (VARCHAR(16),auditTime,120) as auditTime from YZ_CRM_Cost where isnull(def1,0)!=1 and id=" + datalist.get("id").toString() + " ")
                        qunxian = ["fy001": '客户费用', "fy002": '侯震', "fy003": cx.totalCost]
                    } else if (bxlx.reimbursementType == '3' && (cjr.creatorCode == 'zl' || cjr.creatorCode.equals("zl"))) {
                        qunxian = ["fy001": '客户费用', "fy002": '赵林', "fy003": 0]
                    } else if (bxlx.reimbursementType == '3' && (cjr.creatorCode == 'aq' || cjr.creatorCode.equals("aq"))) {
                        qunxian = ["fy001": '客户费用', "fy002": '部门经理', "fy003": 0]
                    } else {
                        //根据创建人查询部门档案是部门经理还是普通员工
                        def ryid = conn.firstRow("select d.def3 from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.id=d.def3 where p.def3='" + cjr.creatorCode + "'")
                        //如果为空说明是普通员工
                        if ((ryid == null || ryid.def3 == null) && bxlx.reimbursementType == '3') {
                            //查询当前人的所属部门
                            def ssbm = conn.firstRow("select d.name from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.dept_id=d.id where p.def3='" + cjr.creatorCode + "'")
                            //判断提报人部门
                            if (bxlx.reimbursementType == '3' && ssbm.name.equals("中高端事业部")) {
                                //客户费用（普通员工）
                                qunxian = ["fy001": '客户费用', "fy002": '中高端事业部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '3' && ssbm.name.equals("客户成功部")) {
                                //客户费用（普通员工）
                                qunxian = ["fy001": '客户费用', "fy002": '客户成功部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '3' && ssbm.name.equals("研发中心")) {
                                //客户费用（普通员工）
                                qunxian = ["fy001": '客户费用', "fy002": '研发中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '3' && ssbm.name.equals("资源中心")) {
                                //客户费用（普通员工）
                                qunxian = ["fy001": '客户费用', "fy002": '资源中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '3' && ssbm.name.equals("商务财务部")) {
                                //客户费用（普通员工）
                                qunxian = ["fy001": '客户费用', "fy002": '商务财务部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '3' && ssbm.name.equals("品控部")) {
                                //客户费用（普通员工）
                                qunxian = ["fy001": '客户费用', "fy002": '品控部', "fy003": 0]
                            }
                        } else {
                            //客户费用（部门经理）
                            qunxian = ["fy001": '客户费用', "fy002": '部门经理', "fy003": 0]
                        }
                    }
                } else if (bxlx.reimbursementType == '2') {//报销类型-项目费用
                    if (bxlx.reimbursementType == '2' && (cjr.creatorCode == "hz" || cjr.creatorCode.equals("hz"))) {
                        qunxian = ["fy001": '项目费用', "fy002": '侯震', "fy003": 0]
                    } else if (bxlx.reimbursementType == '2' && (cjr.creatorCode == 'zl' || cjr.creatorCode.equals("zl"))) {
                        qunxian = ["fy001": '项目费用', "fy002": '赵林', "fy003": 0]
                    } else if (bxlx.reimbursementType == '2' && (cjr.creatorCode == 'aq' || cjr.creatorCode.equals("aq"))) {
                        qunxian = ["fy001": '项目费用', "fy002": '部门经理', "fy003": 0]
                    } else {
                        //根据创建人查询部门档案是部门经理还是普通员工
                        def ryid = conn.firstRow("select d.def3 from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.id=d.def3 where p.def3='" + cjr.creatorCode + "'")
                        //如果为空说明是普通员工
                        if ((ryid == null || ryid.def3 == null) && bxlx.reimbursementType == '2') {
                            //查询当前人的所属部门
                            def ssbm = conn.firstRow("select d.name from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.dept_id=d.id where p.def3='" + cjr.creatorCode + "'")
                            //判断提报人部门
                            if (bxlx.reimbursementType == '2' && ssbm.name.equals("中高端事业部")) {
                                //项目费用（普通员工）
                                qunxian = ["fy001": '项目费用', "fy002": '中高端事业部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '2' && ssbm.name.equals("客户成功部")) {
                                //项目费用（普通员工）
                                qunxian = ["fy001": '项目费用', "fy002": '客户成功部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '2' && ssbm.name.equals("研发中心")) {
                                //项目费用（普通员工）
                                qunxian = ["fy001": '项目费用', "fy002": '研发中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '2' && ssbm.name.equals("资源中心")) {
                                //项目费用（普通员工）
                                qunxian = ["fy001": '项目费用', "fy002": '资源中心', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '2' && ssbm.name.equals("商务财务部")) {
                                //项目费用（普通员工）
                                qunxian = ["fy001": '项目费用', "fy002": '商务财务部', "fy003": 0]
                            }
                            if (bxlx.reimbursementType == '2' && ssbm.name.equals("品控部")) {
                                //项目费用（普通员工）
                                qunxian = ["fy001": '项目费用', "fy002": '品控部', "fy003": 0]
                            }
                        } else {
                            //项目费用（部门经理）
                            qunxian = ["fy001": '项目费用', "fy002": '部门经理', "fy003": 0]
                        }
                    }
                }
            } else {
                mypara.msg("不允许提交")
            }
            SendBean sb = new SendBean()
            sb = new SendBean();
            sb.setGnnid(datalist.get("id").toString());
            sb.setFunctioncode("expense");
            sb.setUsercode(mypara.set.user.code);
            sb.setOrgcode(mypara.set.defaultorg.code.toString());
            sb.setHmvalues(qunxian)
            Map<String, Object> hs = FacesContext.getCurrentInstance().getExternalContext().getSessionMap();
            hs.put("sb", sb)
            Map<String, Object> options = new HashMap<String, Object>();
            options.put("modal", true);
            options.put("draggable", false);
            options.put("resizable", false);
            options.put("contentWidth", 350);
            options.put("contentHeight", 650);
            org.primefaces.PrimeFaces.current().dialog().openDynamic("/appsys/sp/tjdialog", options, null);
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-29
     * 方法作用：审批流返回结果。
     * 方法参数：
     * 返回值：
     * */
    def reimbursementType//报销类型
    def constatus//审核状态

    def handleReturn(SelectEvent event) {
        def conncrm = null
        def connyzcrm = null
        try {
            conncrm = groovy.sql.Sql.newInstance(mypara.hqcon(1))
            connyzcrm = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            BackBean b = (BackBean) event.getObject();
            if (b.flag.equals("1")) {
                //根据返回下级审批节点code，查找审批人发送审批消息
                def subordinate = conncrm.firstRow("select spusercode from [dbo].[wsd_spjl] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and gnnid=" + datalist.get("id") + " and spnodecode='" + b.nxnodecode + "'")
                def spr = connyzcrm.firstRow("select name from [dbo].[YZ_CRM_Psndoc] where def3='" + subordinate.spusercode + "' and psnstatus in(1,2,4)")
                //审批流提交成功后修改单据状态为‘提交’状态
                connyzcrm.execute "update YZ_CRM_Cost set constatus=1,def6='" + spr.name + "' where ID=" + datalist.get("id") + ""
                //查询当前单据编号
                def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                def bxlx = connyzcrm.firstRow("select reimbursementType from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                //获取报销类型对应的名称
                if (bxlx.reimbursementType == '1') {
                    reimbursementType = '公共费用'
                } else if (bxlx.reimbursementType == '2') {
                    reimbursementType = '项目费用'
                } else if (bxlx.reimbursementType == '3') {
                    reimbursementType = '客户费用'
                } else if (bxlx.reimbursementType == '4') {
                    reimbursementType = '合同前费用'
                }
                java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd")
                def nowtime = formatter.format(new Date())//当前时间
                //给下一级审批人发消息
                def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + subordinate.spusercode + "' and psnstatus in(1,2,4)")
                def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + datalist.get("id") + "")
                def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                def neirong = '''"title":"审批通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>''' + tjr.creator + '''提交的费用单，单号''' + bianhao.ccode + '''请您审批</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=6","btntxt":"详情"'''
                qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 0, mypara.set.user.code, subordinate.spusercode, "费用单审批通知", "费用单号:" + bianhao.ccode + "[待审批]")
                mypara.msg("提交成功")
            } else {
                mypara.msg("提交失败")
            }
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conncrm != null) {
                conncrm.close()
            }
            if (connyzcrm != null) {
                connyzcrm.close()
            }
        }
        select(" and a.id=" + datalist.get("id") + " ");
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-31
     * 方法作用：审批流执行。
     * 方法参数：
     * 返回值：
     * */

    def sp() {
        def conn = null;
        def qunxian = [:]
        try {
            conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            def cx = conn.firstRow("select id,htid,ccode,reportingTime,rycode,informant,departCode,department,constatus,code,cusCode,cusName,totalCost,reimbursementType,def1,def2,def3,def4,def5,def6,def7,def8,def9,def10,def11,def12,def13,def14,def15,def16,creatorCode,creator,creationtime,modifier,modifiedtime,reviewer,CONVERT (VARCHAR(16),auditTime,120) as auditTime from YZ_CRM_Cost where def1=0 and id=" + datalist.get("id").toString() + " ")
            def ssbm = conn.firstRow("select d.name from [dbo].[YZ_CRM_Psndoc] p left join [dbo].[YZ_CRM_Dept] d on p.dept_id=d.id where p.def3='" + cx.creatorCode + "'")
            //判断如果报销类型为公共费用
            if (cx.reimbursementType == '1') {
                if (cx.reimbursementType == '1' && cx.informant.equals("赵林")) {
                    qunxian = ["fy001": '公共费用', "fy002": '赵林', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && cx.informant.equals("侯震")) {
                    qunxian = ["fy001": '公共费用', "fy002": '侯震', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && cx.informant.equals("安桥")) {
                    qunxian = ["fy001": '公共费用', "fy002": '部门经理', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && ssbm.name.equals("客户成功部")) {
                    qunxian = ["fy001": '公共费用', "fy002": '客户成功部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && ssbm.name.equals("中高端事业部")) {
                    qunxian = ["fy001": '公共费用', "fy002": '中高端事业部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && ssbm.name.equals("研发中心")) {
                    qunxian = ["fy001": '公共费用', "fy002": '研发中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && ssbm.name.equals("资源中心")) {
                    qunxian = ["fy001": '公共费用', "fy002": '资源中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && ssbm.name.equals("商务财务部")) {
                    qunxian = ["fy001": '公共费用', "fy002": '商务财务部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '1' && ssbm.name.equals("品控部")) {
                    qunxian = ["fy001": '公共费用', "fy002": '品控部', "fy003": cx.totalCost]
                }
            } else if (cx.reimbursementType == '3') {
                //判断如果报销类型为客户费用
                if (cx.reimbursementType == '3' && cx.informant.equals("赵林")) {
                    qunxian = ["fy001": '客户费用', "fy002": '赵林', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && cx.informant.equals("侯震")) {
                    qunxian = ["fy001": '客户费用', "fy002": '侯震', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && cx.informant.equals("安桥")) {
                    qunxian = ["fy001": '客户费用', "fy002": '部门经理', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && ssbm.name.equals("客户成功部")) {
                    qunxian = ["fy001": '客户费用', "fy002": '客户成功部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && ssbm.name.equals("中高端事业部")) {
                    qunxian = ["fy001": '客户费用', "fy002": '中高端事业部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && ssbm.name.equals("研发中心")) {
                    qunxian = ["fy001": '客户费用', "fy002": '研发中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && ssbm.name.equals("资源中心")) {
                    qunxian = ["fy001": '客户费用', "fy002": '资源中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && ssbm.name.equals("商务财务部")) {
                    qunxian = ["fy001": '客户费用', "fy002": '商务财务部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '3' && ssbm.name.equals("品控部")) {
                    qunxian = ["fy001": '客户费用', "fy002": '品控部', "fy003": cx.totalCost]
                }
            } else if (cx.reimbursementType == '4') {
                //判断如果报销类型为合同前费用
                if (cx.reimbursementType == '4' && cx.informant.equals("赵林")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '赵林', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && cx.informant.equals("侯震")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '侯震', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && cx.informant.equals("安桥")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '部门经理', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && ssbm.name.equals("客户成功部")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '客户成功部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && ssbm.name.equals("中高端事业部")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '中高端事业部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && ssbm.name.equals("研发中心")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '研发中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && ssbm.name.equals("资源中心")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '资源中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && ssbm.name.equals("商务财务部")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '商务财务部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '4' && ssbm.name.equals("品控部")) {
                    qunxian = ["fy001": '合同前费用', "fy002": '品控部', "fy003": cx.totalCost]
                }
            } else if (cx.reimbursementType == '2') {
                //判断如果报销类型为项目费用
                if (cx.reimbursementType == '2' && cx.informant.equals("赵林")) {
                    qunxian = ["fy001": '项目费用', "fy002": '赵林', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && cx.informant.equals("侯震")) {
                    qunxian = ["fy001": '项目费用', "fy002": '侯震', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && cx.informant.equals("安桥")) {
                    qunxian = ["fy001": '项目费用', "fy002": '部门经理', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && ssbm.name.equals("客户成功部")) {
                    qunxian = ["fy001": '项目费用', "fy002": '客户成功部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && ssbm.name.equals("中高端事业部")) {
                    qunxian = ["fy001": '项目费用', "fy002": '中高端事业部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && ssbm.name.equals("研发中心")) {
                    qunxian = ["fy001": '项目费用', "fy002": '研发中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && ssbm.name.equals("资源中心")) {
                    qunxian = ["fy001": '项目费用', "fy002": '资源中心', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && ssbm.name.equals("商务财务部")) {
                    qunxian = ["fy001": '项目费用', "fy002": '商务财务部', "fy003": cx.totalCost]
                } else if (cx.reimbursementType == '2' && ssbm.name.equals("品控部")) {
                    qunxian = ["fy001": '项目费用', "fy002": '品控部', "fy003": cx.totalCost]
                }
            }
            SendBean sb = new SendBean()
            sb = new SendBean()
            sb.setGnnid(datalist.get("id").toString());
            sb.setFunctioncode("expense");
            sb.setUsercode(mypara.set.user.code);
            sb.setOrgcode(mypara.set.defaultorg.code.toString());
            sb.setJq(true)
            sb.setGdjq(true)
            sb.setJc(false)
            sb.setHmvalues(qunxian)
            Map<String, Object> hs = FacesContext.getCurrentInstance().getExternalContext().getSessionMap();
            hs.put("sb", sb)
            Map<String, Object> options = new HashMap<String, Object>();
            options.put("modal", true);
            options.put("draggable", false);
            options.put("resizable", false);
            options.put("contentWidth", 350);
            options.put("contentHeight", 650);
            org.primefaces.PrimeFaces.current().dialog().openDynamic("/appsys/sp/spdialog", options, null);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close()
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-29
     * 方法作用：审批流审批过程返回结果。
     * 方法参数：
     * 返回值：
     * */

    def handleReturn2(SelectEvent event) {
        def conncrm = null;
        def connyzcrm = null;
        def csr = [];
        def pjyt = "";//审批人拼接
        try {
            conncrm = groovy.sql.Sql.newInstance(mypara.hqcon(1))
            connyzcrm = groovy.sql.Sql.newInstance(mypara.hqcon(2))
            BackBean b = (BackBean) event.getObject();
            java.text.SimpleDateFormat formatter2 = new java.text.SimpleDateFormat("yyyy-MM-dd")
            java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm")
            def nowtime = formatter2.format(new Date())//当前时间
            //审批同意
            if (b.flag.equals("3")) {
                def subordinate = "select spusercode from [dbo].[wsd_spjl] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and gnnid=" + datalist.get("id") + " and spnodecode='" + b.nxnodecode + "' and spresult=0"
                def subordinate2 = conncrm.firstRow("select spusercode from [dbo].[wsd_spjl] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and gnnid=" + datalist.get("id") + " and spnodecode='" + b.nxnodecode + "'")
                conncrm.eachRow(subordinate) {
                    def spr = "select name from [dbo].[YZ_CRM_Psndoc] where def3='" + it.spusercode + "' and psnstatus in(1,2,4)"
                    def pjlist = connyzcrm.rows(spr);
                    if (pjlist == []) {
                        return pjyt;
                    }
                    for (i in pjlist) {
                        pjyt += i.name + ",";
                    }
                }
                //拼接审批人
                if (!pjyt.isEmpty()) {
                    pjyt = pjyt.substring(0, (pjyt.length() - 1));
                } else {
                    pjyt = "该审批节点没有人员信息";
                }
                //pjyt = (pjyt.toString()).substring(0, (pjyt.toString().length() - 1));
                //审批流提交成功后修改单据状态为‘审核中’状态
                connyzcrm.execute "update YZ_CRM_Cost set constatus=2,def6='" + pjyt + "' where ID=" + datalist.get("id") + ""
                //单据编号查询
                def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                def bxlx = connyzcrm.firstRow("select reimbursementType from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                //获取报销类型对应的名称
                if (bxlx.reimbursementType == '1') {
                    reimbursementType = '公共费用'
                } else if (bxlx.reimbursementType == '2') {
                    reimbursementType = '项目费用'
                } else if (bxlx.reimbursementType == '3') {
                    reimbursementType = '客户费用'
                } else if (bxlx.reimbursementType == '4') {
                    reimbursementType = '合同前费用'
                }
                //查询企业微信号
                def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + subordinate2.spusercode + "' and psnstatus in(1,2,4)")
                //查询费用总金额
                def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + datalist.get("id") + "")
                //查询提交人
                def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                //拼接信息，企业微信发送消息
                def neirong = '''"title":"审批通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>''' + tjr.creator + '''提交的费用单，单号''' + bianhao.ccode + '''请您审批</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=8","btntxt":"详情"'''
                qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 0, mypara.set.user.code, subordinate2.spusercode, "费用单审批通知", "费用单号:" + bianhao.ccode + "[待审批]")
                mypara.msg("审批成功");
            }
            //加签审批
            if (b.flag.equals("5")) {
                def subordinate = "select spusercode,gnnid from [dbo].[wsd_spjl] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and gnnid=" + datalist.get("id") + " and spnodecode='" + b.nxnodecode + "' and spresult=0 and spusercode !='" + mypara.set.user.code + "'"
                conncrm.eachRow(subordinate) {
                    def bianhao = connyzcrm.firstRow("select ccode,def6 from YZ_CRM_Cost where ID=" + it.gnnid + "")
                    def bxlx = connyzcrm.firstRow("select reimbursementType from YZ_CRM_Cost where ID=" + it.gnnid + "")
                    def spr = connyzcrm.firstRow("select name from [dbo].[YZ_CRM_Psndoc] where def3='" + it.spusercode + "' and psnstatus in(1,2,4)")
                    //审批流提交成功后修改单据状态为‘审核中’状态
                    connyzcrm.execute "update YZ_CRM_Cost set constatus=2,def6='" + bianhao.def6 + "," + spr.name + "' where id=" + it.gnnid + " "
                    //获取报销类型对应的名称
                    if (bxlx.reimbursementType == '1') {
                        reimbursementType = '公共费用'
                    } else if (bxlx.reimbursementType == '2') {
                        reimbursementType = '项目费用'
                    } else if (bxlx.reimbursementType == '3') {
                        reimbursementType = '客户费用'
                    } else if (bxlx.reimbursementType == '4') {
                        reimbursementType = '合同前费用'
                    }
                    //发消息
                    def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + it.spusercode + "' and psnstatus in(1,2,4)")
                    def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + it.gnnid + "")
                    def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + it.gnnid + "")
                    def neirong = '''"title":"审批加签通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>''' + tjr.creator + '''提交的费用单，单号''' + bianhao.ccode + '''请您审批</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=8","btntxt":"详情"'''
                    qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                    SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 5, mypara.set.user.code, it.spusercode, "费用单加签审批通知", "费用单号:" + bianhao.ccode + "[待审批]")
                    mypara.msg("加签完成");
                }
            }
            //驳回完毕
            if (b.flag.equals("6")) {
                connyzcrm.execute "update YZ_CRM_Cost set constatus=4,def6='审批未通过' where ID=" + datalist.get("id") + ""
                def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                def qywx = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + tjr.creatorCode + "' and psnstatus in(1,2,4)")
                def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                def neirong = '''"title":"驳回通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>您提交的费用单，单号''' + bianhao.ccode + '''未审批通过</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                qiyeweixinmsg(qywx.WeComAccount, neirong)
                SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 3, mypara.set.user.code, tjr.creatorCode, "您提交费用单", "费用单号:" + bianhao.ccode + "[已驳回]")
                mypara.msg("驳回成功");
            }
            //审批结束后进行抄送，查询抄送人、审批流发起人
            if (b.flag.equals("10")) {
                def name = mypara.getSet().getUser().name//当前登录人
                def time = formatter.format(new Date())//当前时间
                //公共费用
                if (datalist.get("reimbursementType") == '1') {
                    def deleteSql = "update YZ_CRM_Cost set constatus=3,auditTime='" + time + "',reviewer='" + name + "',def6='审批通过' where id=" + datalist.get("id") + "; "
                    datalist.put("constatus", 3);
                    datalist.put("auditTime", time);
                    datalist.put("reviewer", name);
                    connyzcrm.execute(deleteSql);
                    //提交人
                    def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                    def qywx = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + tjr.creatorCode + "' and psnstatus in(1,2,4)")
                    //费用单号
                    def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                    def neirong2 = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>您提交的费用单，单号''' + bianhao.ccode + '''审批通过</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                    qiyeweixinmsg(qywx.WeComAccount, neirong2)
                    //查询抄送人
                    def end = conncrm.firstRow("select csusers from [dbo].[wsd_spnode] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and xh=999")
                    if (end != null) {
                        def qzf = end.csusers.replace("[", "")
                        qzf = qzf.replace("]", "")
                        if (qzf.split(",").size() == 1) {
                            csr.add(qzf)
                        } else {
                            csr = qzf.split(",")
                        }
                    }
                    csr.each {
                        if (!it.trim().equals("" + tjr.creatorCode + "")) {
                            def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + it.trim() + "' and psnstatus in(1,2,4)")
                            if (qywxzh != null) {
                                def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + datalist.get("id") + "")
                                def neirong = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>报销类型-公共费用,金额''' + je.totalCost + '''元,提报人''' + tjr.creator + ''',请知晓</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                                qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                            }
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, tjr.creatorCode, "您提交费用单", "费用单号:" + bianhao.ccode + "[已通过]")
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, it.trim(), "" + tjr.creator + "提交的费用单", "费用单号:" + bianhao.ccode + "[请查阅]")
                            mypara.msg("审批完成");
                        }
                    }
                }
                //项目费用
                if (datalist.get("reimbursementType") == '2') {
                    def deleteSql = "update YZ_CRM_Cost set constatus=3,auditTime='" + time + "',reviewer='" + name + "',def6='审批通过' where id=" + datalist.get("id") + "; "
                    //查询合同中的项目费用
                    def fyxmfy = connyzcrm.firstRow("select totalCost from YZ_CRM_Cost where id=" + datalist.get("id") + " ")
                    def htxmfy = connyzcrm.firstRow("select projectfee from YZ_CRM_Contract where code='" + datalist.get("code") + "' ")
                    if (htxmfy == null || htxmfy.projectfee == null) {
                        htxmfy.projectfee = 0
                    }
                    def jine = 0
                    BigDecimal aa = new BigDecimal(fyxmfy.totalCost.toString())
                    BigDecimal bb = new BigDecimal(htxmfy.projectfee.toString())
                    jine = aa + bb
                    deleteSql = deleteSql + "update YZ_CRM_Contract set projectfee=" + jine + " where code='" + datalist.get("code") + "'; "
                    datalist.put("constatus", 3);
                    datalist.put("auditTime", time);
                    datalist.put("reviewer", name);
                    connyzcrm.execute(deleteSql);
                    def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                    def qywx = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + tjr.creatorCode + "' and psnstatus in(1,2,4)")
                    def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                    def neirong2 = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>您提交的费用单单号''' + bianhao.ccode + '''审批通过</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                    qiyeweixinmsg(qywx.WeComAccount, neirong2)
                    def end = conncrm.firstRow("select csusers from [dbo].[wsd_spnode] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and xh=999")
                    if (end != null) {
                        def qzf = end.csusers.replace("[", "")
                        qzf = qzf.replace("]", "")
                        if (qzf.split(",").size() == 1) {
                            csr.add(qzf)
                        } else {
                            csr = qzf.split(",")
                        }
                    }
                    csr.each {
                        if (!it.trim().equals("" + tjr.creatorCode + "")) {
                            def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + it.trim() + "' and psnstatus in(1,2,4)")
                            if (qywxzh != null) {
                                def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + datalist.get("id") + "")
                                def neirong = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>报销类型-项目费用,金额''' + je.totalCost + '''元,提报人''' + tjr.creator + ''',请知晓</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                                qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                            }
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, tjr.creatorCode, "您提交费用单", "费用单号:" + bianhao.ccode + "[已通过]")
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, it.trim(), "" + tjr.creator + "提交的费用单", "费用单号:" + bianhao.ccode + "[请查阅]")
                            mypara.msg("审批完成");
                        }
                    }
                }
                //客户费用
                if (datalist.get("reimbursementType") == '3') {
                    def fyxmfy = connyzcrm.firstRow("select totalCost from YZ_CRM_Cost where id=" + datalist.get("id") + " ")
                    def htxmfy = connyzcrm.firstRow("select custfee from YZ_CRM_Contract where code='" + datalist.get("code") + "' ")
                    if (htxmfy == null || htxmfy.custfee == null) {
                        htxmfy.custfee = 0
                    }
                    def jine = 0
                    BigDecimal aa = new BigDecimal(fyxmfy.totalCost.toString())
                    BigDecimal bb = new BigDecimal(htxmfy.custfee.toString())
                    jine = aa + bb
                    def deleteSql = "update YZ_CRM_Cost set constatus=3,auditTime='" + time + "',reviewer='" + name + "',def6='审批通过' where id=" + datalist.get("id") + "; "
                    deleteSql = deleteSql + "update YZ_CRM_Contract set custfee=" + jine + " where code='" + datalist.get("code") + "'; "
                    datalist.put("constatus", 3);
                    datalist.put("auditTime", time);
                    datalist.put("reviewer", name);
                    connyzcrm.execute(deleteSql);
                    def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                    def qywx = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + tjr.creatorCode + "' and psnstatus in(1,2,4)")
                    def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                    def neirong2 = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>您提交的费用单单号''' + bianhao.ccode + '''审批通过</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                    qiyeweixinmsg(qywx.WeComAccount, neirong2)
                    def end = conncrm.firstRow("select csusers from [dbo].[wsd_spnode] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and xh=999")
                    if (end != null) {
                        def qzf = end.csusers.replace("[", "")
                        qzf = qzf.replace("]", "")
                        if (qzf.split(",").size() == 1) {
                            csr.add(qzf)
                        } else {
                            csr = qzf.split(",")
                        }
                    }
                    csr.each {
                        if (!it.trim().equals("" + tjr.creatorCode + "")) {
                            def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + it.trim() + "' and psnstatus in(1,2,4)")
                            if (qywxzh != null) {
                                def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + datalist.get("id") + "")
                                def neirong = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>报销类型-客户费用,金额''' + je.totalCost + '''元,提报人''' + tjr.creator + ''',请知晓</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                                qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                            }
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, tjr.creatorCode, "您提交费用单", "费用单号:" + bianhao.ccode + "[已通过]")
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, it.trim(), "" + tjr.creator + "提交的费用单", "费用单号:" + bianhao.ccode + "[请查阅]")
                            mypara.msg("审批完成");
                        }
                    }
                }
                //合同前费用
                if (datalist.get("reimbursementType") == '4') {
                    def deleteSql = "update YZ_CRM_Cost set constatus=3,auditTime='" + time + "',reviewer='" + name + "',def6='审批通过' where id=" + datalist.get("id") + "; "
                    datalist.put("constatus", 3);
                    datalist.put("auditTime", time);
                    datalist.put("reviewer", name);
                    connyzcrm.execute(deleteSql);
                    def tjr = connyzcrm.firstRow("select creatorCode,creator from YZ_CRM_Cost where id=" + datalist.get("id") + "")
                    def qywx = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + tjr.creatorCode + "' and psnstatus in(1,2,4)")
                    def bianhao = connyzcrm.firstRow("select ccode from YZ_CRM_Cost where ID=" + datalist.get("id") + "")
                    def neirong2 = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>您提交的费用单单号''' + bianhao.ccode + '''审批通过</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                    qiyeweixinmsg(qywx.WeComAccount, neirong2)
                    def end = conncrm.firstRow("select csusers from [dbo].[wsd_spnode] where orgcode='" + mypara.set.defaultorg.code.toString() + "' and functioncode='expense' and xh=999")
                    if (end != null) {
                        def qzf = end.csusers.replace("[", "")
                        qzf = qzf.replace("]", "")
                        if (qzf.split(",").size() == 1) {
                            csr.add(qzf)
                        } else {
                            csr = qzf.split(",")
                        }
                    }
                    csr.each {
                        if (!it.trim().equals("" + tjr.creatorCode + "")) {
                            def qywxzh = connyzcrm.firstRow("select WeComAccount from [dbo].[YZ_CRM_Psndoc] where def3='" + it.trim() + "' and psnstatus in(1,2,4)")
                            if (qywxzh != null) {
                                def je = connyzcrm.firstRow("select totalCost from [dbo].[YZ_CRM_Cost] where id=" + datalist.get("id") + "")
                                def neirong = '''"title":"抄送通知","description":"<div class=\'gray\'>''' + nowtime + '''</div><div class=\'highlight\'>报销类型-合同前费用,金额''' + je.totalCost + '''元,提报人''' + tjr.creator + ''',请知晓</div>","url":"http://js.jnuser.com:8077/wsdsjmcloud/resources/huoquid.jsp?type=7","btntxt":"详情"'''
                                qiyeweixinmsg(qywxzh.WeComAccount, neirong)
                            }
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, tjr.creatorCode, "您提交费用单", "费用单号:" + bianhao.ccode + "[已通过]")
                            SpUtuils.sendMsg(mypara.set.defaultorg.code.toString(), "expense", datalist.get("id").toString(), 4, mypara.set.user.code, it.trim(), "" + tjr.creator + "提交的费用单", "费用单号:" + bianhao.ccode + "[请查阅]")
                            mypara.msg("审批完成");
                        }
                    }
                }
            }
        } catch (Exception e) {
            println(e.toString())
        } finally {
            if (conncrm != null) {
                conncrm.close()
            }
            if (connyzcrm != null) {
                connyzcrm.close()
            }
        }
        select(" and a.id=" + datalist.get("id") + " ");
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-29
     * 方法作用：调用企业微信接口发消息。
     * 方法参数：
     * 返回值：
     * */

    def qiyeweixinmsg(String code, String neirong) {
        CloseableHttpClient httpClient = HttpClients.createDefault();
        CloseableHttpResponse httpResponse = null;
        String responseContent = null;
        HttpGet hh = new HttpGet("https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=wwc744fdb07650dccd&corpsecret=O5tjbmgdPhrC1J7cBkkD2Fo7cMPmw-z9VLEaMaNMx5o");
        try {
            //调用HttpClient对象的execute方法执行请求;
            httpResponse = httpClient.execute(hh);
            //获取请求响应对象和响应Entity;
            HttpEntity httpEntity = httpResponse.getEntity();
            //从响应对象中获取响应状态，从响应Entity中获取响应内容;
            if (httpEntity != null) {
                responseContent = EntityUtils.toString(httpEntity, "UTF-8");
            }
            def slurper = new JsonSlurper()
            def states = slurper.parseText(responseContent)
            def access_token = states['access_token']
            String json = '''{"touser" : "''' + code + '''","msgtype" : "textcard","agentid" : 1000021,"textcard" : {''' + neirong + '''},"enable_id_trans": 0,"enable_duplicate_check": 0,"duplicate_check_interval": 1800}'''
            HttpPost ss = new HttpPost("https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=" + access_token + "")
            StringEntity se = new StringEntity(json, "UTF-8");
            ss.setEntity(se);
            ss.setHeader("Accept", "application/json");
            ss.setHeader("Content-Type", "application/json;charset=utf-8");
            httpResponse = httpClient.execute(ss);
            HttpEntity httpEntity1 = httpResponse.getEntity();
            //6、从响应对象中获取响应状态，从响应Entity中获取响应内容;
            if (httpEntity != null) {
                responseContent = EntityUtils.toString(httpEntity1, "UTF-8");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭响应对象;
                if (httpResponse != null) {
                    httpResponse.close();
                }
                //关闭HttpClient.
                if (httpClient != null) {
                    httpClient.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    /*
     * 创建人：肖兴顺
     * 创建时间：2022-03-16
     * 方法作用：审批权限。
     * 方法参数：
     * 返回值：
     * */

    def qxkz(def djlx, def sql, def nid, def functioncode, def usercode, def cjr) {
        def rtu = 0
        if (usercode.equals("sjm")) {
            return rtu = 1
        } else {
            if (usercode.equals(cjr.toString())) {
                return rtu = 1
            }
            def spz = sql.firstRow("select b.zcode from [dbo].[YZ_CRM_jurisdiction] a left join [dbo].[YZ_CRM_Appcontrol] b on a.id=b.jid where code='" + djlx + "' and control like '%" + 1 + "%'")
            if (spz != null) {
                def zqx = sql.firstRow("select count(*) as cc from [dbo].[wsd_usertogroup] where usercode='" + usercode + "' and groupcode='" + spz.zcode + "'")
                if (zqx.cc > 0) {
                    return rtu = 1
                }
            }
            def ljcount = sql.firstRow("select count(nid) as cc from [dbo].[wsd_spjl] where functioncode='" + functioncode + "' and gnnid=" + nid + " and spusercode='" + usercode + "'")
            if (ljcount.cc > 0) {
                return rtu = 1
            }
            def tjr = sql.firstRow("select count(nid) as cc from [dbo].[wsd_spmsg] where functioncode='" + functioncode + "' and gnnid=" + nid + " and (fromusercode='" + usercode + "' or tousercode='" + usercode + "')")
            if (tjr.cc > 0) {
                return rtu = 1
            }
        }
        return rtu
    }
}
